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.

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

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

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

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

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

.Value

used in place of

.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

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

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

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

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

Hi Mumps,
Your suggestion works!
Thanks again for taking all the trouble in the world.