Copy contents of multi-sheet workbook into another workbook

Jesa1984 · May 16th 2017

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.

Code

Sub Import()




Dim N As Long
Dim Entry_Book As String
Dim First_Row As Integer
Dim Last_Row As Long
Dim Last_Row1 As Long




Application.DisplayAlerts = False
Application.ScreenUpdating = False
Entry_Book = ThisWorkbook.Name




Last_Row1 = Workbooks(Entry_Book).Sheets("Item Lookup").Range("A" & Rows.Count).End(xlUp).Row




Workbooks.Open Filename:="UPC List.xls"
Windows("UPC List.xls").Activate




For N = 1 To ActiveWorkbook.Worksheets.Count
    If N = 1 Then
        First_Row = Workbooks("UPC List.xls").Sheets(N).Range("B1").End(xlDown).Row
    Else
        First_Row = 1
    End If
    Last_Row = Workbooks("UPC List.xls").Sheets(N).Range("B" & Rows.Count).End(xlUp).Row 'debug error is here when N = 3
    Workbooks("UPC List.xls").Sheets(N).Range("B" & First_Row & ":D" & Last_Row).Copy
    Workbooks(Entry_Book).Sheets("Item Lookup").Range("A" & Last_Row1).PasteSpecial Paste:=xlPasteAll
    Last_Row1 = Workbooks(Entry_Book).Sheets("Item Lookup").Range("A" & Rows.Count).End(xlUp).Row + 1
Next N




Windows("UPC List.xls").Close SaveChanges = False




End Sub

Display More

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!

Logit · May 16th 2017

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.

Jesa1984 · May 16th 2017

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

forum.ozgrid.com/index.php?attachment/72490/

Seems as if the number of rows doesn't matter. I wanted to make sure it would still error and it does on the 3rd tab.

Logit · May 16th 2017

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:

Code

Option Explicit




Sub Import()
     
     
    Dim N As Long
    Dim Entry_Book As String
    Dim First_Row As Integer
    Dim Last_Row As Long
    Dim Last_Row1 As Long
     
     
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Entry_Book = ThisWorkbook.Name
     
     
    Last_Row1 = Workbooks(Entry_Book).Sheets("Item Lookup").Range("A" & Rows.Count).End(xlUp).Row
     
     
    Workbooks.Open Filename:="C:\Users\My\Desktop\New folder\UPC List.xls"  '<-- change path to suite your system
    Windows("UPC List.xls").Activate
     
     
    For N = 1 To ActiveWorkbook.Worksheets.Count
        If N = 1 Then
            First_Row = Workbooks("UPC List.xls").Sheets(N).Range("B1").End(xlDown).Row
        Else
            First_Row = 1
        End If
        Last_Row = Workbooks("UPC List.xls").Sheets(N).Range("B" & Rows.Count).End(xlUp).Row 'debug error is here when N = 3
        Workbooks("UPC List.xls").Sheets(N).Range("B" & First_Row & ":D" & Last_Row).Copy
        Workbooks(Entry_Book).Sheets("Item Lookup").Range("A" & Last_Row1).PasteSpecial Paste:=xlPasteAll
        Last_Row1 = Workbooks(Entry_Book).Sheets("Item Lookup").Range("A" & Rows.Count).End(xlUp).Row + 1
    Next N
     
     
    Windows("UPC List.xls").Close
     
     
End Sub

Display More

Jesa1984 · May 16th 2017

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.

Logit · May 17th 2017

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.

Jesa1984 · May 17th 2017

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

Quote from Logit;792361

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

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

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.

Display More

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?

Logit · May 17th 2017

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

Doubtful the code is 'running too fast' .

The macro works fine here.

Jesa1984 · May 17th 2017

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

Can I drop box you the whole file?

Logit · May 17th 2017

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

Certainly.

Jesa1984 · May 17th 2017

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

Sent you a PM

Logit · May 17th 2017

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 ?

Jesa1984 · May 17th 2017

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.

Logit · May 17th 2017

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 ?

Logit · May 17th 2017

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

Jess

Thank you for the kind comments.

Jesa1984 · May 17th 2017

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?

Jesa1984 · May 18th 2017

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

bump

Copy contents of multi-sheet workbook into another workbook

Participate now!

Share