Macro Copy&Paste

Question

I'm trying to create a macro that will copy data from one worksheet and place into another. This I can do with no problem. But, when I want to use the same macro in another row is where I have my problem. Basically what I want to do is copy cell D11 from sheet1 and place that in cell B4 on sheet2, etc (What I'm doing is obviously more complicated than that, but that doesn't matter here).

My problem is when I want to now run this macro and copy cell D12 from sheet1 and paste into B5 on sheet2 the value pasted jumps to B4. I understand that this happens because of where the VBcode is saying to paste the copied value.

My question is how to I just have it paste in whatever row I choose? Maybe based on what row/cell I have selected.

Current code, written by recording the macro

Sheets("sheet1").Select
Range("D11").Select
Selection.Copy
Sheets("sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
Range("B4").Select

I'm assuming the last line is where I need to make the change, but I'm not sure what to change. Thank you! Any and all help is greatly appreciated.

Yeah you have everything hardcoded currently, if you want to just use the selected cell and make it relative to the row (lets say you want to offset the paste by 7 rows since 12-5=7) you can just have `Selection.Copy` and then `Sheets("sheet2").Range("B" & Selection.Row-7).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False` — chancea, Dec 22 '14 at 22:54
You are right, as well as the other comment. However, I wouldn't get into approaching it like this. Once you are into VBA and working with code, and not just recording macros, you will want to avoid using Select Statements to move and copy data. Check out this Q&A: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — peege, Dec 22 '14 at 23:01
If you could post more about the context of what you are trying to accomplish, there might be a much simpler, elegant way to code that. Or perhaps just more adaptable to future needs. Otherwise, the solution provided in the comment is on the right track. — peege, Dec 22 '14 at 23:03

score 1 · Answer 1 · edited May 23 '17 at 10:25

As a general rule, try to avoid Selection Copy-Paste (detailed discussion is provided in: "Application.Calculation = xlCalculationManual" statement causing run-time error 1004 in VBA Copy-Paste procedure). Instead, use direct copy statement, which will solve you issue and significantly improve performance:

Listing 1.

Sub DirectCopySample()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("D11").Copy Destination:=Sheets("Sheet2").Range("B5")
    Application.ScreenUpdating = True
End Sub

Sub in Listing 1 performs direct copy from Cell: Sheets("Sheet1").Range("D11") into cell: Sheets("Sheet2").Range("B5").

Also, your initial Copy-Paste Sub could be simplified (it will also make it work, though Listing 1 is preferred)

Listing 2.

Sub CopyPasteSample()
    Sheets("sheet1").Range("D11").Copy
    Sheets("sheet2").Range("B5").PasteSpecial Paste:=xlPasteValues
End Sub

Hope this will help. Best regards,

@ThatGuy17: in case you are satisfied with the answer, would you please mark it accepted? Thanks and regards, — Alexander Bell, Dec 23 '14 at 16:53

score 0 · Answer 2 · answered Dec 23 '14 at 01:29

You seem to have recorded a Macro and are trying to replay it. Here is a real VBA code (not a Macro recording type):

Sheets("sheet2").Range("B5") = Sheets("sheet1").Range("D11").Value

This is all!

BTW, your predicament comes from the fact that the PasteSpecial method copies into the currently selected cell. You've tried running this Macro several times and the Range("B4").Select line did the trick. If you insist on your approach the insert Range("B5").Select BEFORE the PasteSpecial.

Macro Copy&Paste

2 Answers2