CODEWITHSUNDEEP
excelvba
Mike
Mike

Reputation: 13

Copying specific cells to another worksheet

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

Answers (1)

SJR
SJR

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

Related Questions