Automate Copying Excel Column Data from Sheet1 to Sheet2 with VBA


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:


16 thoughts on “Automate Copying Excel Column Data from Sheet1 to Sheet2 with VBA

  1. Tony

    Hi can you please help I have created a userform which on opening the workbook is showing on sheet2 but I want it to open and stay on sheet1 and when I enter data I want it to go onto sheet2 in rows. I have given names to the three sheet tabs at the bottom which is confusing

    Reply
  2. nelie

    Thank you very much for all your tutorials! They are very helpful!!

    I have used your sample to start my document but now every time I press ‘Copy Data’, it duplicates the information every time.

    Can you kindly advise on how to not have duplicates please.

    Reply
  3. Rajan

    Dear Sir

    when I try to run the above above code I get an error in the 3rd last row. I have copied the code as mention above. Pls help
    Sheet2.Columns().AutoFit
    run time error ‘424’ object not defined

    Reply
    1. benomar

      You could change it by the following line codes :
      Worksheets(“Sheet2”).Columns(“A:F”).AutoFit
      Sheets(“sheet2”).Select
      Sheets(“sheet2”).Range(“A1”).Select

      Reply
      1. Anthony Johnson

        I’ve attached the working code. There was a slight change on the last Range statement. Also, if you copy/paste the code from this page, some characters, like “, will not paste correctly. It is best to copy/paste the code to a notepad to remove any special characters, then copy into Excel. This gentleman’s code is some of the best I’ve seen – clean and simple.

        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
        ActiveSheet.Range(“A1”).Select
        End Sub

        Reply
  4. niveditha

    hello sir, great lessons!
    This code is giving me a Runtime error, subscript out of range error.

    I have simply copy pasted this.

    I know this sounds silly, but being a beginner Im just not able to figure out what the issue is

    Can anybody help me, please?

    Thanks so so much!!!

    Reply
  5. Bill

    I am having a problem with this code. Here is what i have:
    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:=Worksheets(“Sheet2”).Cells(erow, 1)

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

    End Sub

    I am getting the “Subscript out of range” error on line 9, I can’t see why it will not Paste.

    Can you PLEASE help me,
    Much thanks in advance,
    Bill

    Reply
  6. Priti Bhaiswar

    Sir I have a vba code and i want modification in that. If I want to copy particular column like column c, column F, Column I, Column K etc. of sheet1 and pest in Shett2 in the Column A, Column D, Column G, Column M etc…..and the filter should be as it is that is given in the following code…
    So would you convey me by my mail i.e. bhaiswarpravin@gmail.com

    Option Explicit

    Sub copyDatabyCondition()
    ‘copy data if column S isn’t empty
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    Dim gSourceRow As Long, gSourceTotal As Long
    Dim gTargetRow As Long

    ‘intialization
    Set wsSource = ThisWorkbook.Sheets(“INPUT”)
    Set wsTarget = ThisWorkbook.Sheets(“Sheet2”)

    ‘find the last row in both tables
    gTargetRow = wsTarget.[A65536].End(xlUp).Row

    With wsSource
    ‘for each row in the source table
    For gSourceRow = 11 To .[A65536].End(xlUp).Row
    ‘column S isn’t empty?
    If .Cells(gSourceRow, “S”) 0 Then
    gTargetRow = gTargetRow + 1 ‘increment row no. & appending a new line
    ‘copying a line from source table to target table
    wsTarget.Range(“A” & gTargetRow & “:N” & gTargetRow).Value = _
    .Range(“L” & gSourceRow & “:Y” & gSourceRow).Value
    End If
    Next
    End With
    End Sub

    Reply
  7. Gurpreet

    Hi,

    I am using the code above for achieving copy and pasting based on the specific criteria. But how do i set the criteria based on the following:

    I have following data in the column:
    D14WS108,D578S108,D72SS108,D14578S105
    And i want the data that start with D14 copied over to tab called D14 and the data that starts with D57 move it over to tab called D57

    I put in the following code for critiera selection but it doesn’t work
    If Sheet1.Cells(i, 26) = “D14” Then

    Can you please help

    Reply
  8. ivo

    Hi,
    I followed advice on how to copy/paste data from one sheet to another based on specific condition. In my case, I have written the macro (which I could send via email), however I believe that due to the number of .select and .activate operations I am getting the error “Run time 1004” method range of object worksheet failed sometimes I also get the run time error 13. DO you know what could be possibly happening? Is there a way of not using .select and .activate for this same example.

    Thanks

    Reply
  9. AMIT GUPTA

    HELLO BRO I WANT TO US VBA IN EXCEL BUT WHEN I CLICK ON BUTTON IT SHOW THE MACRO MAY NOT BE AVAILABLE IN THIS WORKBOOK OR MAY BE DISABLE
    BUT IN MY EXCEL MACRO IS ENABLE AND I SAVED IN MACRO ENABLE EXCEL SHEET FORMAT
    WHATS THE PROBLEM PLEASE HELP ME OUT

    Reply
  10. Pingback: Automate Copying of Column Data from Sheet to Sheet Using Excel VBA | Videos Kiss

  11. Sanket

    Hello,
    I tried these three methods and they didn’t work

    str1 = “C” & CStr(i + a) & “:D” & CStr(i + a)
    str2 = “B” & CStr(lastrow + i)

    ThisWorkbook.Sheets(“Sheet2”).Range(str1).Select
    Selection.Copy
    ThisWorkbook.Sheets(“Sheet1”).Range(str2).Select
    ActiveSheet.Paste

    ‘ Sheets(“Sheet2”).Select
    ‘ Range(str1).Select
    ‘ Selection.Copy
    ‘ Sheets(“Sheet1”).Select
    ‘ Range(str2).Select
    ‘ ActiveSheet.Paste

    ‘ ws2.Range(ws2.Cells(3, i + a), ws2.Cells(4, i + a)).Copy Destination:=ws1.Range(ws1.Cells(lastrow + i, 2), ws1.Cells(lastrow + i, 2))
    ‘ MsgBox ws1.Range(ws1.Cells(lastrow + i, 2), ws1.Cells(lastrow + i, 3)).Value
    ‘ ws1.Cells(lastrow + i, 2) = ws2.Cells(i + a, 3).Select

    Non of them are working

    Reply
  12. ladydee94

    thanx for the tutorial and the script. i have one question, how about i want use inputbox for the selected column and inputbox for the text that we want to filter. i just want to add inputbox for user doesn’t need to make change at vb code view. user can choose their selected column and text using inputbox. thank you.

    Reply
  13. elnaz

    hello thank you for your excellent topics
    is it possible to change this macro to paste cells that contain formula from one sheet to another?
    when i use this macro it show #ref in my destination cells!!!

    Reply

Leave a Reply

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