Reputation: 13
Good Day,
I am trying to copy specific cells from one worksheet to another. In this case I need to copy 4 cells(B4,B14,B22,G22) of sheet one into cells (R5,R15,R24,W24)of the second sheet on a button click.
Private Sub CommandButton1_Click()
Sheets("Approver").Select
Range("B4").Select
Selection.Copy
Sheets("Request").Select
Sheets("Request").Range("R5").PasteSpecial Paste:=xlPasteValues
End Sub
I Can get the first field copying fine but when I try to do all 4 its throwing back errors. I assume there is something I am missing to get this working? Any help is greatly appreciated.
Upvotes: 0
Views: 69
Reputation: 23081
One way is like so - loop through an array of addresses. Plus, you can transfer values directly, slightly more efficient than copying and pasting values. And another thing, avoid Select
wherever possible.
Private Sub CommandButton1_Click()
Dim v1, v2, i As Long
v1 = Array("B4", "B14", "B22", "G22")
v2 = Array("R5", "R15", "R24", "W24")
For i = LBound(v1) To UBound(v1)
Sheets("Request").Range(v2(i)).Value = Sheets("Approver").Range(v1(i)).Value
Next i
End Sub
Upvotes: 1