Hi all,
I have been working on the VBA for a little while now, and have figured it out. There are default headers, and the macro data starts on row2 for everything.
<tbody>
</tbody>
I'm wondering if there is an easier or more desirable way to write this VBA? Below is what I have done so far that works, but it does take a while to run.
I've seen some other people asking for similar advice, and the VBA that I have seen from mumps and other pros looks different.
I have been working on the VBA for a little while now, and have figured it out. There are default headers, and the macro data starts on row2 for everything.
Columns | |||||||||
Sheet1=Destination | A | B | C | F | I | L | P | S | V |
Sheet2=Source | D | E | C | J | R | S | AA | AC | AI |
<tbody>
</tbody>
I'm wondering if there is an easier or more desirable way to write this VBA? Below is what I have done so far that works, but it does take a while to run.
I've seen some other people asking for similar advice, and the VBA that I have seen from mumps and other pros looks different.
Code:
Sub copycol()
Dim lastrow As Long, erow As Long
Application.ScreenUpdating = False
'to check the last filled line on sheet named CMF DB
lastrow = Worksheets("CMF DB").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Worksheets("CMF DB").Cells(i, 4).Copy
' How many Rows are already filled
erow = Worksheets("Recon Master").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 1)
Worksheets("CMF DB").Cells(i, 5).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 2)
Worksheets("CMF DB").Cells(i, 3).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 3)
Worksheets("CMF DB").Cells(i, 10).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 6)
Worksheets("CMF DB").Cells(i, 18).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 9)
Worksheets("CMF DB").Cells(i, 19).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 12)
Worksheets("CMF DB").Cells(i, 27).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 16)
Worksheets("CMF DB").Cells(i, 29).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 19)
Worksheets("CMF DB").Cells(i, 35).Copy
Worksheets("CMF DB").Paste Destination:=Worksheets("Recon Master").Cells(erow + 1, 22)
Next i
Application.ScreenUpdating = True
End Sub