Automating Copy And Paste

[bloodmilksky]

Hey Guys,

I was just wondering if anyone knows how to run the below for every row that has a value init on Sheet 1?

so it would be

E2,C2,L2,P2,Q2

E3,C3,L3,P3,Q3

E4,C4,L4,P4,Q4

E5,C5,L5,P5,Q5

E6,C6,L6,P6,Q6

E7,C7,L7,P7,Q7 and so on it there is no values left. These cells would always be copied into the same range on Sheet 2 E2- B7 C2-B21 L2-C21 P2-D21 Q2-E21 I have attached a very rudimentry macro for it but didnt know if there is a quicker way of doing it. Sheets("Sheet1").Select Range("E2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("B7").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("C2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("B21").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("L2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("C21").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("P2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("D21").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("Q2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("E21").Select ActiveSheet.Paste Range("E26").Select Sheets("Sheet2").Select Range("B7").Select Selection.ClearContents Sheets("Sheet1").Select Range("E3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("B7").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("C3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("B21").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("L3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("C21").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("P3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("D21").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("Q3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("E21").Select ActiveSheet.Paste

[Paul_Hossler]

You can clean up the macro recorder code a lot

Usually you do not need to .Select something to use it

Option Explicit
Sub Test()
    With Sheets("Sheet1")
        .Range("E2").Copy Sheets("Sheet2").Range("B7")
        .Range("C2").Copy Sheets("Sheet2").Range("B21")
        
'       repeat above
        
        
    End With
    Application.CutCopyMode = False

End Sub

[mdmackillop]

Sub Test()
Dim arrA()
Dim arrB()
Dim i As Long


arrA = Array("E2", "C2", "L2")
arrB = Array("B7", "B21", "C21")


For i = 0 To 2
Sheet1.Range(arrA(i)).Copy Sheet2.Range(arrB(i))
Next i
End Sub

[bloodmilksky]

Thank you Guys Really Appreciate it.