Copy Column Data Automatically from Excel Sheet1 to Excel Sheet2 with VBA


We have learnt how to transfer data from rows from one Excel worksheet to another. How can we copy column data automatically from Excel Sheet1 to Excel sheet2 with VBA. We can refine the process of extracting specific data from specific columns and then transferring the data to a new worksheet using VBA. This automation of copying data from one worksheet to another Excel worksheet can help to analyze data and create excellent reports quickly and easily.

The automated process of copying data from columns of one Excel worksheet to another involves the following steps:

  • Decide into which worksheet you wish to copy the data
  • Clear this sheet of all data to avoid duplication
  • Assign appropriate headers to the appropriate cells like Product Name to Range A1 and so on
  • Now activate the sheet from where you wish to get the data
  • Based on a condition or conditions loop through all the data
  • Capture the data that meets the criteria into variables
  • Now go to the sheet where the data is to be placed
  • Find the first blank row in that sheet
  • Place the captured data into appropriate cell range(s) via the variables
  • Go back to the data sheet
  • Keep on looping through all the data in this manner
  • Make sure the data in the ‘report’ sheet is viewable by auto fitting the columns
  • Come back to the data sheet

Watch the training video before you start studying the Excel VBA code:

You can view the video also on YouTube.


Download a sample file by clicking on the Excel icon:


The complete VBA code to automate the process of copying data from columns from one Excel sheet to another:
Sub copypastecolumndata()
Sheet2.Select
Sheet2.Cells.ClearContents
Range(“a1”).Value = “Product Name”
Range(“B1”).Value = “Price (Indian Rupees)”

Sheet1.Select
Dim Product_Name As String
Dim Product_Price As Single
Dim lastrow As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 2) = “Notebook” And Cells(i, 3) >= 20000 Then
Product_Name = Sheet1.Cells(i, 1)
Product_Price = Sheet1.Cells(i, 3)
Sheet2.Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet2.Cells(erow, 1) = Product_Name
Sheet2.Cells(erow, 2) = Product_Price
Range(“A:B”).Columns.AutoFit
Sheet1.Activate
End If
Next i
End Sub