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

7 thoughts on “Copy Column Data Automatically from Excel Sheet1 to Excel Sheet2 with VBA

  1. Eiger67

    Namaste Takyar

    I would like implement this code with an Inputbox, i think to change the product to find in the VBA editor is to limited and is not work that anyone can do.
    Please don’t get offened if i do this, i consider you as a big teacher.

    Here my pice of code what do you think about it:

    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
    Dim i As Long
    Dim sSearch As String
    Dim eRow As Long

    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    sSearch = InputBox(“Please select the Product to find”)

    Set x = ActiveSheet.Columns(“B”).Find( _
    sSearch, LookIn:=xlValues, lookat:=xlWhole, _
    searchorder:=xlByRows)
    If x Is Nothing Then
    MsgBox “The selected Product is not available!” & vbCrLf & _
    “Pleas select another one.” & vbCrLf & _
    “Thank you”, vbInformation
    Exit Sub
    End If
    For i = 2 To lastRow

    If Cells(i, 2) = x And Cells(i, 3) >= 100 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

    Have a nice day

    Eiger67

    Reply
  2. Quoit1

    Hello Dinesh! Thank you for your videos. Question: How would you modify this code such that if sheet1 is full, paste column data in sheet2. If sheet2 has data, paste in sheet3 so on. I am looking for something like:
    if Sheet2 Range(“B3”) “”
    then Sheet2.Cells(erow,1) = Product_Name and Sheet2.Cells(erow,3) = Product_Price

    Else go to Sheet3…so on. This way I can run code multiple times transferring column data on different sheets. Thanks! Quoit1

    Reply
  3. Afaque

    I would like to generate a code where it copies date to a specific sheet based on a user form to another sheet which is linked to a model.

    For example: my sheet would compose of multiple projects and six monthly results. These results changes based on percentage of completion for each project. The user can change result based on percentage of completion for each project. further, I would like to control percentage of completion should not exceed 100% for any given project.

    Please send me your email address.

    Thanks in advance

    Afaque

    Reply
  4. YOGESH LAD

    Dear Sir,
    pls help,
    i have one data excel file sheet 1 all data , sheet 2 VLOOKUP & data printing box
    i am vlookup doing in sheet2 only 2nd row .i want to each code vlookup data copy to sheet3.

    Reply
  5. carlos

    “did exatcly like you did but i get
    run-time error ‘424’ object required”

    i discovered what i did wrong! In the vba program I need to substitute Sheet1 and Sheet2 for the equivalent of my excel language which is Folha1 and Folha2

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *