I had published this video ‘Automate copying of column data from one worksheet to another using Excel VBA’ on YouTube https://youtu.be/1OtJeS0NWCo
After watching the training video Debi Putnam had the following question:
This is exactly what I need. However, when I run the macro, it is writing over the same row instead of moving down a row. Here is my code. Can you tell me what I’m doing wrong?

She was using the following code:

Sub copycolumns()
Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Sheet2.Cells(erow, 7)
Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Sheet2.Cells(erow, 24)
Sheet1.Cells(i, 5).Copy
Sheet1.Paste Destination:=Sheet2.Cells(erow, 25)
Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select
End Sub

My original code was:

Sub copycolumns()

Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
If Sheet1.Cells(i, 6) = “Maharashtra” Then
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)

Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)

Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 3)
End If

Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select

End Sub

Apart from the fact that I was using a condition to copy data from columns in Sheet1 to columns in Sheet2, my erow variable took reference to column 1 and also pasted the data from sheet1 into sheet2 starting from column 1:

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)

Debi was copying data from sheet1 and pasting into different columns and also not starting in column 1:

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Sheet2.Cells(erow, 7)

Now every time the ‘for next’ loop runs, it finds there is no data in column1 and the relevant row. It keeps on copying the data in the columns in row 2. Watch the training video to understand the solution to Debi’s problem: