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

1. 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

2. 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.

3. 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

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

      1. 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

4. 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!!!

5. 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

   1. I am also getting the same error message… Any response please let me know as well. Thanks in advance!

6. 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. [email protected]

   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

7. I have formulas in the sheet 1 cells and want to paste them as values. How do I do that? Please?

8. 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

9. 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

10. 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

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

12. 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

13. 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.

14. 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!!!

15. Hello, I have a different situation where I’ve to do the following:
    1. Workbook1 has 5 worksheets, and each worksheet has same layout (name of the columns and row count).
    I have to select just a few columns from every sheet(A, C, D K, T, Z) from each worksheet and paste it below each other in a new worksheet in Workbook2.
    2. Workbook1 is closed and I’m trying to fetch data from it by using the ADO connection.

    I’ve not been able to figure this out yet after trying a lot.
    Could you please help me with this?
    Any help would be appreciated!

    Thank you
    Abhi

16. I want to generate receipt for my customers when i enter their data into sheet one and click print. Here I have some more information to collect from client side. but only need to print their name , mob and some other details into receipt. how can I do this with excel VBA?

17. Hi

    I am looking to be able to automatically move a row of data from A – I from one sheet (Action Log) to another sheet (closed Actions) When the status in cells column I is changed to complete. can you please help me with this problem?

    Kind regards,

    Julie Ann

18. Where’s the sample file?

19. Sir i contacted you one of the post.
    I need your help urgently.
    Am. Sharing my email address. Would mind to.talk to me. So i can provide what is my requirment. Amd a sample data. Pls pls.

20. Dear Sir,
    Very help full post. I was searching for this since very long. All working fine but only issue is that when i change the sheet names according to my excel code not working. My sheets name are “Master Collection” and Todays collection. I do not want to change my sheet. Also please help me with current month formula. My sheet has got date (do not want to Add month in excel) and i want code to copy data which falls under current month. Awaiting your response

21. Dear, I have an excel workbook having excel three excel sheet, named 1) Item List, 2) INVOICE and 3) INVOICELIST. In INVOICE sheet I want to create 2 no’s of button by using VBA CODE, 1) save button by clicking only invoice contents as per invoice no., date, party name and sale details as per item wise, will be transfer to in a row but in a column basis, means if I select ten no’s of item in INVOICE Sheet to prepare Invoice by Clicking on SAVE BUTTON, Invoice row wise selected items would be transferred appear permanently as per invoice no., date, consumer Name to a row but in ten column for ten items of invoice in INVOICE LIST sheet. 2) NEW INVOICE button, on which by clicking next invoice no. would be automatically generating for preparing new invoice, and previous invoice contents would have cleared except FORMULA in INVOICE Sheet. In INVOICE LIST sheet, where DATA of INVOICE sheet would be appearing permanently, as per invoice no., date, consumer, May I get help to solve the problem?

    here i have use code as given below

    Private Sub CommandButton1_Click ()

    Dim INVOICENO As String
    Dim BUYER As Single
    Dim COMMODITITY As Single
    Dim QUANTITY As Single
    Dim AMOUNT As Single
    Dim MyDATA As Workbook

    Worksheets(“sheet1”). Select
    COMMODITY = Range(“C1”)
    Worksheets(“sheet1”). Select
    AMOUNT = Range(“C9”)
    Worksheets(“sheet1”). Select
    BUYER = Range(“C4”)
    Worksheets(“sheet1”). Select
    QUANTITY = Range(“C6”)
    Worksheets(“sheet1”). Select
    INVOICENO = Range(“C2”)
    End If

    End Sub

Comments are closed.