How to automate copying of column data from sheet to sheet using Excel VBA. We have earlier learnt how to transfer row data from one worksheet to another with Excel VBA. Sometimes you may need to copy specific column data from one worksheet to another quickly and automatically. The Excel VBA code below explains step by step how to perform such a transfer of column data from one worksheet to another. Before studying the VBA code watch the training video:

You can watch the video also on YouTube.
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
Download a sample Excel file: