Problem moving sheets beyond 23 or so...

rensonnet

New Member
Joined
Dec 7, 2005
Messages
2
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :eek: ). 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,996
Messages
5,834,784
Members
430,322
Latest member
excelnoobnoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top