PDA

View Full Version : [SOLVED] Automating Copy And Paste



bloodmilksky
04-12-2017, 09:25 AM
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
04-12-2017, 12:51 PM
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
04-13-2017, 02:41 PM
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
04-18-2017, 05:59 AM
Thank you Guys Really Appreciate it.