VBA script can't combine my excel sheets

  • Hi,

    Just a newbie here, not really experienced in coding or such, instead I pulled a script from the internet to serve one purpose.

    Which is to combine all the sheets in my excel file onto one single sheet where all the data will be collated. It works for some file, but at the same time, it doesn't for some, due to empty spaces in between, may I know if there is a solution to this?


    Attached is my test file, any help is appreciated.


    Files

  • You need to remove all those rows with only numbers in column A, otherwise they will be copied


  • All your data sheets need to be formatted the same.

    • The data should start in the same row and have identical headers in the same order.
    • To use CurrentRegion the table of data should be have an empty row at the top between the header row and any summaries or sheet title. In your case A6 should be empty
    • You should not have unused rows in the data either with formulas in or, as in your workbook, id numbers with no actual data
  • You need to remove all those rows with only numbers in column A, otherwise they will be copied


    You are a blessing, this worked out so well after deleting column A. Thank you so much!

  • You need to remove all those rows with only numbers in column A, otherwise they will be copied



    As for the column A, is there any way to in-corporate the following code into your vba code? Such that every time I do not have to delete column A in 10+ sheets. Once again, thank you very much for your assistance


    Code
    1. Sub sbVBS_To_Delete_EntireColumn ()
    2. Columns(1).EntireColumn.Delete
    3. End Sub
  • As for the column A, is there any way to in-corporate the following code into your vba code? Such that every time I do not have to delete column A in 10+ sheets. Once again, thank you very much for your assistance


    Code
    1. Sub sbVBS_To_Delete_EntireColumn ()
    2. Columns(1).EntireColumn.Delete
    3. End Sub

    Actually you know what, I looked through the training on the website, and I got it all figured out. Thank you very much, just found this website out today, and it is really useful for people like me. I would love to learn VBA to do stuff like these myself.