Problem moving sheets beyond 23 or so...

[rensonnet]

There have already been one or two posts with a similar problem, but no resolution, so I thought I might try again and see if anyone has new info.

I'm moving sheets from various workbooks into one summary workbook and I repeatedly get a "Runtime error 1004: Move Method of Worksheet Class Failed" on the 23rd or so move. (I need about 50 or so).

Here is the code that is failing:

Function movetest()

PPRDIR = "C:\Documents and Settings\arensonnet\Desktop\PPR stuff\"
AreaRepName = "PPR Template - test.xls"

For Each c In Worksheets("private").Range("A2:A45").Cells
areaname = ThisWorkbook.Worksheets("private").Cells(c.Row, 1).Value
If Dir(PPRDIR & AreaRepName) <> "" Then
Workbooks.Open Filename:=PPRDIR & AreaRepName, UpdateLinks:=False
sourceWB = Workbooks(AreaRepName).Name
Workbooks(sourceWB).Sheets(areaname).Move before:=Workbooks("MOVEtest.xls").Sheets("Sheet1")
Workbooks("MOVEtest.xls").Sheets(areaname).Unprotect Password:="avenge"
Workbooks("MOVEtest.xls").BreakLink Name:=PPRDIR & AreaRepName, Type:=xlExcelLinks
Count = Count + 1
Workbooks(sourceWB).Close (False)
End If
Next c

End Function

Don't get distracted by the uselessness of this particular code. It's extracted from a much larger macro to do some tests to figure out why the move method is failing. I don't really open the same document repeatedly to retrieve the sheets in my macro; I open a different document each time. But this is much easier to work with for testing purposes.

Just to test it, I tried opening the document before looping. That gets me 44 moves before the code breaks. If inside the loop, I get to 23. Thinking that this is a memory/buffer issue, I added the break links statement, and that didn't do anything for me.

Note; the loop is done over a matrix in the "private" sheet containing all the names of the sheets that need to be moved.

I have also tried using the copy method, with not difference.

Thank you for any suggestions.
Anne

 

[BrianB]

I do not know if this is the reason, and if it is I have forgotten how to overcome it (How about that for a disclaimer - I should work for Microsoft ). I thought we could change the CodeName but Excel says "ReadOnly".

More important to Excel than the name we give a worksheet is its Code Name. This is set when a sheet is first made and does not change in the normal course of events. Useful if we wish to refer to a sheet in code when we think the name might have been changed.

So we use code like this :-
MsgBox Worksheets(1).CodeName
MyValue=Sheet1.Range("A1")

If we keep copying Sheet1 which might have a code name of Sheet1, its code name changes to Sheet11, then Sheet111 ... etc. See this in the Project Explorer. The point here is that there is a limit to the length of a code name. Once that limit is reached the next attempt to copy produces an error. Some time ago I came across this and the limit always seemed to be around 40 sheets to a workbook.

Hope this helps.