Macro to Copy Column data and insert on another sheet

  • Hi!
    I have a macro that copies data from Column D of Sheet2 and pastes values in Column B of Sheet1.

    Code
    Sub CommandButton1_Click()
        Sheets("Sheet2").Columns("D").Copy Sheets("Sheet1").Range("B1")
    End Sub


    However, I want that before pasting the values, the macro should insert a new column so that existing values in Column B of Sheet1 are shifted to the right and values are pasted in the empty Col. B of Sheet1
    Alternately, if someone could suggest a different solution to copy values from Cells D1 to D10 of Sheet2 and insert these as a new column B in Sheet1 (after shifting existing columns to the right).
    Please note that all the above will happen in background automatically and the Macro will be run from some other sheet so the Active Sheet need not be Sheet1 or Sheet2
    Thanks

  • Re: Macro to Copy Column data and insert on another sheet


    Try:

    Code
    Sub CopyRange()
        Sheets("Sheet1").Columns("B:B").Insert Shift:=xlToRight
        Sheets("Sheet2").Range("D2:D10").Copy Sheets("Sheet1").Range("B1")
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Macro to Copy Column data and insert on another sheet


    Code
    Sub CommandButton1_Click()      
    Sheets(1).Activate
    ActiveSheet.Columns("B").Insert
    Sheets(2).Columns("D").Copy ActiveSheet.Columns("B")
    End Sub
  • Re: Macro to Copy Column data and insert on another sheet


    Hi Mumps,
    You code works as requested.
    However, I forgot to mention that the data in Sheet2 is actually the result of a formula, so the code is currently copying the formula to Sheet1 not the actual values.
    Can you please modify it to paste absolute values from cells in Sheet2 to Sheet1 (values can be numbers in upto 3 decimals or even text). Also, the values can be blank or the value in the cell can be a calculation error too (#N/A), but the code should paste whatever is available in the cells at the time


    Patel: Your code works, but it uses the Activate command, therefore making my screen jump to Sheet1 every time it is executed, whereas my request was for background execution.
    Thanks for your support guys.

  • Re: Macro to Copy Column data and insert on another sheet


    Try:

    Code
    Sub CopyRange()
        Sheets("Sheet1").Columns("B:B").Insert Shift:=xlToRight
        Sheets("Sheet2").Range("D2:D10").Copy
        Sheets("Sheet1").Range("B1").PasteSpecial xlPasteValues
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Macro to Copy Column data and insert on another sheet


    Quote from Mumps;679840

    Try:


    Yup, That works!!
    But brings a new problem, viz. the cells being copied and pasted remain in memory selection (with the dashed line appearing around the cells being copied, as is usual in Excel when we copy-paste something, and the pasted cells too remain selected). The cells being copied therefore continue to remain in memory.
    If I press Ctrl+V i.e. paste, the cells copied are pasted as formulas in another place. This was not the case with your earlier code.
    Also, there is a violent screen flash when the code is started and executed. The code

    Code
    Application.ScreenUpdating = False
    ...code...
    Application.ScreenUpdating = True


    fails to stop the violent screen flashing.
    I guess the code

    Code
    .Value

    used in place of

    Code
    .PasteSpecial xlPasteValues

    while selecting the range should help, but am not sure how to use .Value with this code.
    Can you advise a way to clear the cells being copied from memory/ selection.

  • Re: Macro to Copy Column data and insert on another sheet


    Add this line to the code:

    Code
    Application.CutCopyMode =False

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Macro to Copy Column data and insert on another sheet


    Almost Perfect, Just Almost...
    Added your suggestion and also removed screen flashing with

    Code
    Application.ScreenUpdating = False 
    ...code... 
    Application.ScreenUpdating = True


    However, the Cells pasted still remain selected when I go to Sheet1 (though not in the memory anymore). Any Idea to remove the selection. Requesting since this was not the case with your original code.
    Will save me an extra click or an extra Esc key press.
    And Oh! Thanks again for your prompt replies.

  • Re: Macro to Copy Column data and insert on another sheet


    You could add this line at the end of the code.

    Code
    Sheets("Sheet1").Range("A1").Select

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Macro to Copy Column data and insert on another sheet


    Quote from Mumps;679866

    You could add this line at the end of the code.

    Code
    Sheets("Sheet1").Range("A1").Select

    Nopes. That doesn't work. Gives some Microsoft for Visual Basic Application 400 error and Run-time error '1400': Select method of Range Class failed error and highlights that code line on debugging, incase the current Active sheet is different from Sheet1. Seems you are trying to set the Active cell as A1 in the background, but works only if the current active sheet is Sheet1. Any other method of setting the active cell as A1 for Sheet1 in the background?

  • Re: Macro to Copy Column data and insert on another sheet


    Could you post a copy of your file so I can have a closer look? If the workbook has any confidential information, you could replace it with generic data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Macro to Copy Column data and insert on another sheet


    No Issues about privacy.
    Am attaching a sample file about which you have been so helpful.
    You can start the Macro by pressing the 'Start' Button on the Live Sheet and Stop it with the Stop Button.
    When you open the sheet, please notice the cells highlighted in yellow colour are selected (on sheet 'Data') when the macro runs. What I was requesting is that it should not be selected by a general outline.
    Also, you would be happy to know that your original formula suddenly became immensely helpful in what I was trying to achieve. A big thank you for that.

  • Re: Macro to Copy Column data and insert on another sheet


    Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Macro to Copy Column data and insert on another sheet


    It was my pleasure. :smile:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!