Copy contents of multi-sheet workbook into another workbook

  • Hi all!
    I'm at a loss here... I have a workbook called UPC List.xls. I'm unable to save it as a .xlsx because it is auto generated and I can only specify Excel and not what version. I open the UPC List.xls workbook and then I go through each tab of that workbook and paste it into a worksheet in another workbook. I get through the first two sheets fine but when it gets to the 3rd sheet (which isn't the last in this instance) of UPC List.xls I get a debug error on the line where I determine the last row of the spreadsheet.




    The problem workbook is too large to attach to this post but essentially, all of the tabs except for the first and the last all have 65,535 rows in them so not sure why it would work on the second tab but not the third.


    Any help would be much appreciated!

  • Re: Copy contents of multi-sheet workbook into another workbook


    Make a copy of your workbooks, removing all but say 100 rows.


    Then post those here for review.


    Don't display any confidential information.

  • Re: Copy contents of multi-sheet workbook into another workbook


    .
    .
    First I created a new folder on my desktop. Name of folder: New folder


    Then placed both files in this new folder.


    Changed your macro to the following, which imported all sheets data:


  • Re: Copy contents of multi-sheet workbook into another workbook


    Looks like the only thing you changed in my code is the opening of the UPC List.xls. I removed the file path just to make it simpler.


    Here's the thing. If I just run the code I don't get an error but all of the data doesn't copy over. It looks like the third through the last tabs are just copying over each other.


    If I put a break in the code at 'Next N' when N = 2 I get the error 'Application-defined or object-defined error' but when N was 1 that line worked fine. Also, for some reason Last_Row1 comes back as a value of 2 when N = 2 but it really should be 131,068.

  • Re: Copy contents of multi-sheet workbook into another workbook


    I placed the name of the sheets at the bottom of each col of data (on each sheet).


    For example: I placed "Sheet 1" at the bottom of Col A for Sheet 1.


    I placed "Sheet 1 (1)" at the bottom of Col A for Sheet 1 (1)


    Etc.


    After running the macro, it copied all of the data as evidenced by each sheet's name displayed within the 800 + rows of data that was copied over.


    I did add the path to the file so it would recognize where to look for the information, otherwise the macro errors out on that line without the path.


    -------------------------


    Just added more data to each sheet. 30,000 + rows each sheet.
    Copied all three sheets no errors.


    Must be something else going on with your workbook.

  • Re: Copy contents of multi-sheet workbook into another workbook



    Could there be an issue with since there are so many rows on each tab that the code runs too fast to catch the last row?

  • Re: Copy contents of multi-sheet workbook into another workbook


    Doubtful the code is 'running too fast' .


    The macro works fine here.

  • Re: Copy contents of multi-sheet workbook into another workbook


    I ran the dropbox copy through several checks and reviews. There is a problem with
    it but I can't determine what it is.


    The problem I am having here with the DropBox download : only copying Col A to the new workbook.
    Col B is being ignored as though it doesn't exist.


    There is nothing wrong with the macro code for copying over the data ... the issue involves the file
    UPC List.xls but again I am not certain what.


    I tried making a fresh version of UPC List in another workbook however it appears whatever issue
    exists was probably copied over to the new version.



    For what it is worth, all rows were copied over ... nothing was left behind. However, only Col A was
    copied ... Col B was not.


    So sorry ....


    Perhaps someone else on the Forum can solve this.


    Anyone else ?

  • Re: Copy contents of multi-sheet workbook into another workbook


    Ok, the file imports without any errors, however, when I run it the Item Lookup tab only has 131,067 rows but the file has 5 tabs all with 65k rows on it.

  • Re: Copy contents of multi-sheet workbook into another workbook


    I just had a thought ... I copied each sheet, one at time and pasted the data into NotePad. Then copied that and pasted into
    a fresh workbook.


    Again, one sheet at a time.


    That apparently got rid of the 'unknown' formatting / error causing issue'.


    Ran the new workbook and it copies everything as expected.


    Having to copy / paste to NotePad / copy / paste to new workbook is tedious to be certain, but it did clear up the mess.


    Your thoughts ?

  • Re: Copy contents of multi-sheet workbook into another workbook


    At this point I just need something that works. Can you provide the macro code that you used?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!