VBA Excel Issue with Copying Workbook in another Workbook - application hangs on Copy on specific files

Question

I have an issue that is honestly putting me at a loss.

I am copying a set of workbooks within a 'master' workbook (copying all the sheets). There are 2 methods I can use, either going through each sheet in the workbooks to copy each and every one in the master or else copy the workbook as a whole and place in the master workbook. I am using the second method using an array to discard sheets I dont need.

Dim ws() As String ' declare string array
ReDim ws(wb.Worksheets.Count) As String ' set size dynamically
Dim counter As Long ' running counter for ws array
counter = 0
For c = 1 to WS_Count
If wb.Worksheets(c).Name <> "TEST" Then
    ws(counter) = wb.Worksheets(c).Name
    counter = counter + 1
End If
Next

ReDim Preserve ws(counter-1) As String wb.Worksheets(ws).Copy Before:=master.Worksheets(master.Worksheets.Count)

Both approaches I have tried work well with certain files however:

1) The first approach is problematic because it leaves a reference to the original file and so i moved to approach 2 which bypasses this issue as no reference is kept.

2) approach 2 is resulting in some sort of infinite loop in a certain file. the funny thing about this is that if i change the order in which they are merged the command doesnt get stuck and with another approx 50 workbooks the codes seems to work just fine. (please note that this issue doesn't occur with method 1 but method 1 has been discarded due to the file links)

The line it simply gets stuck on (no error) is wb.Worksheets(ws).Copy Before:=master.Worksheets(master.Worksheets.Count)

Did anyone ever encounter this issue with a file not wanting to merge? Did i hit some limit somewhere? I'm at a loss because using 26 different workbooks, my code in its entirity managed to create a master workbook of 896 sheets. In this set that's getting stuck, i'm merging a 164 worksheet file with a new worksheet containing 164 files. I am using Office Professional Plus 2010.

I am currently hiding alerts, however I believe i was getting on of those messages where it asks me if i want to wait for the application.

Anyone can point me in the right direction please?

Your code is confusing to me, but here's a couple of thoughts. Assuming you haven't set Option Base 1, your ws array has one more element than wb.Worksheets.Count. I prefer to do something like: ReDim ws(1 to wb.Worksheets.Count), currently it's the same as ReDim ws(0 to wb.Worksheets.Count). Also, you should be able to get rid of Counter, since, like c, it's just incrementing by 1. — Doug Glancy
I have an initial ReDim which is doing what you are saying however I am then removing the blank entries (i.e. the Test pages). The counter is keeping track of pages which need to be copied i.g. ignoring pages called 'TEST'. — Erika
This is a lot of sheets, I wouldn't be surprised if too much memory is being used. As an aside, un the first method (which I don't think you have shown) - you can resolve the link issue by linking the destination workbook back to itself to replace the source links — brettdj
Sorry, of course, on the counter. Careless reading on my part. — Doug Glancy
@MP24 dictionary.Keys() or dictionary.Values() both return an array :) But yes, the # of sheets seems the likely problem here. — David Zemens

Chuck Dickens Chuck Dickens · Accepted Answer · 2014-07-10T13:54:22

Since you are skipping "Test" pages have you tried starting with the last page and stepping -1?

For c = WS_Count to 1 

    If wb.Worksheets(c).Name <> "TEST" Then
        ws(counter) = wb.Worksheets(c).Name
        counter = counter - 1
    End If
Next c

VBA Excel Issue with Copying Workbook in another Workbook - application hangs on Copy on specific files

1 Answers