Macro stops after 76 worksheet copies? (Excel 2000)

[JohnBF]

If your objective is to print the sheets listed in non-hidden rows in a summary sheet, why do all that copying? This is a bit crude, but here's something you could try:

Sub printsheetsfromlist()
Dim rngNames As Range
Dim strGroup As String
Dim wks As Worksheet
Dim intC As Integer, intN As Integer, intS As Integer

Application.ScreenUpdating = False
Set wks = ThisWorkbook.ActiveSheet ' get active sheet
' get the non-hidden sheet names, assuming the list of sheets to be printed
' is in a sheet called "Sheetlist"
With ThisWorkbook.Worksheets("Sheetlist")
Set rngNames = .Range(.<!t>[A2]<!/t>, .<!t>[A65536]<!/t>.End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
intN = rngNames.Cells.Count

' examine each sheet name for validity, add it to string
For intC = 1 To intN
On Error Resume Next
Worksheets(rngNames.Cells(intC, 1).Value).Select ' check that exists
If Not Err.Number Then
strGroup = strGroup & Chr(34) & rngNames.Cells(intC, 1).Value & Chr(34)
If intC < intN Then strGroup = strGroup & ", "
End If
Next intC
' ask for number of copies
intS = Application.InputBox(Prompt:="How many copies?", Default:="1", Type:=1)
' print the selected worksheets
ThisWorkbook.Worksheets(Array(strGroup)).PrintOut Copies:=intS
wks.Activate ' return to original active sheet
Application.ScreenUpdating = True
Set wks = Nothing
End Sub

It doesn't have all of your trimmings, such as setting the calc mode.

[Frank Castle]

John,

Thanks for the suggestion, but for various reasons, I have to copy the sheets into a "Temp" workbook that is isolated and separate from the original file.

Frank

[Frank Castle]

Almost 6,000 characters of code moved to attachment by HansV. If you want to post large pieces of code, please do so in the form of an attached text file.

I

[JohnBF]

See if this blows up after some number of sheets:

Sub printsheetsfromlist()
Dim rngSheetNameList As Range, rngCell As Range
Dim strSName As String
Dim wks As Worksheet
Dim wbkTemp As Workbook
Dim intC As Integer, intN As Integer, intS As Integer

Application.ScreenUpdating = False
Set wks = ThisWorkbook.ActiveSheet
' get the non-hidden sheet names
' assuming they are on a sheet called Sheetlist
With ThisWorkbook.Worksheets("Sheetlist")
Set rngSheetNameList = .Range(.<!t>[A2]<!/t>, .<!t>[A65536]<!/t>.End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
intN = rngSheetNameList.Cells.Count
Set wbkTemp = Workbooks.Add

' examine each sheet name for validity, copy it
For Each rngCell In rngSheetNameList
strSName = rngCell.Text
With ThisWorkbook
On Error Resume Next
.Worksheets(strSName).Select ' check it exists
If Not Err.Number Then _
.Worksheets(strSName).Copy After:=wbkTemp.Sheets(wbkTemp.Worksheets.Count)
End With
Next rngCell

Application.DisplayAlerts = False
' delete initial sheets in temp workbook
With wbkTemp
For intC = 1 To .Worksheets.Count - intN
.Worksheets(intC).Delete
Next intC
End With
' ask for number of copies
intS = Application.InputBox(Prompt:="How many copies?", Default:="1", Type:=1)
' print the selected worksheets
wbkTemp.PrintOut Copies:=intS
wbkTemp.Close ' discard the temporary book

wks.Activate ' reactivate original worksheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set wks = Nothing
Set wbkTemp = Nothing
End Sub

[Frank Castle]

Hans,

Thanks for the code. I plugged it into the file and, sure enough, it stops copying after 76 sheets, just like in the original code. Doesn't matter how the list is sorted, or where I start from (if I change your code to start from A10 rather than A2, for example), it gets to 76 and just stops copying - it continues to run and loop through the code, just seems to ignore the copy command.

As it was working, I was stepping through it to check variables and such. It was accurate in knowing how many visible cells, and therefore sheets, there were (140), etc. It worked just fine except that it stopped copying after 76 sheets. Weird...

I even tried running it on a completely different computer - neither mine nor that of the guy who actually "owns" the file. It still stopped at 76 sheets (the third computer has same specs as mine, so I guess that's to be expected).

Is there any chance that this could be some issue having to do with the clipboard? Like maybe it's getting cluttered up with junk while the macro is working, since all this copying is going on? Is there any way to clear the clipboard after copying each sheet?

Thanks again for your help.

Frank

[JohnBF]

Frank, I think it's a resource issue. In a test I was able to copy 179 of 256 sheets, although in my test each copied sheet contained only one cell of data, using XP/Office 2003 on a 150 ghz machine with 256 MB ram with Outlook 2003 running, but no other applications. The only simple code modification I can think of to try is to add the line in red:

If Not Err.Number Then _
.Worksheets(strSName).Copy After:=wbkTemp.Sheets(wbkTemp.Worksheets.Count)
<font color=red>Application.CutCopyMode = False</font color=red>

The next thing might be to save the temporary workbook every time a sheet is copied to it: that will be a bit more code, I'll put something together and attach it to this post later.

I also think that you should reconsider my first approach that doesn't require the creation of a temporary workbook and all the copying.

P.S., I'm not Hans, I'm much slower and I get seven hours of sleep a night. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

[LegareColeman]

There are a couple of things that could be causing this problem, but without a copy of the workbook to test with it is impossible to tell which is causing the problem. I will suggest one thing that might work for you. Instead of copying individual sheets to a new workbook, make a copy of the workbook file. Then go through all of the sheets in the file and delete sheets that are not in the visible list.

[Frank Castle]

John,

Sorry about calling you Hans. I gets confused.

I tried the same thing: made a fake workbook with 100+ sheets, two cells of data per sheet. Worked just peachy, copied all 100+ sheets. Has to be a resource issue.

I tried saving the file after each sheet copy - it didn't make a difference in terms of getting beyond the 76 sheet limit.

Sadly, the macro has to copy the sheets to a temp file. This gentleman runs reports that consist of the sheets printed in various groupings, sometimes organized by people in each region, sometimes by manager etc.

I built a "baby" version of the macro that went to each sheet and sent it to the printer. Unfortunately 1) the process of spooling 100+ print jobs would generally put him in low performance pergatory for 30 minutes and 2) while his jobs were waiting to be printed, nobody else's could print.

If I selected multiple sheets simultaneously and used the corresponding option in the print dialog box to print the active sheets, it did not slow down his machine or logjam the printer.

Unfortunately, the sheets print in the order they appear in the workbook, not the order in which you select them. So if you have sheets alphabetically arranged, you could select W T A Z in that order but they print in the order A T W Z, forcing him to collate by hand.

The only solution was to copy the sheets in the desired order into a temp workbook, which he could print.

He also wanted the "temp" workbook to be on the network while his original copy remained on his C: drive.

Thanks again for your help. At this point, I feel confident in saying this is a resource issue.

Frank

[Frank Castle]

Legare,

Sorry, I can't post the file - proprietary data full of customer names, e-mail addresses, etc.

When you say there are a couple of potential reasons for the problem, can you point me in any direction i.e. is it a memory issues, etc?

Your idea about duplicating the file and manipulating the duplicate is interesting - I'll give that a try. Thanks!

Frank.

[JohnBF]

I think Legare's solution of copying the entire book and then deleting the unwanted sheets is an excellent one, I'll work up some code on it and post it later. To test the macro without tying up the printer, comment out the PrintOut line.

[LegareColeman]

The things that come to mind are:

1- A memory leak that is causing available memory to become depleted.

2- An object naming problem. When Excel copy sheets using the method you used, it can create sheet object names (not the name that appears on the sheet tab, the name that is not in parenthesis in the VBA Project Explorer) that become progressively long and eventually exceed the max length.

3- Corruption in the workbook.

The first two can be circumvented by inserting a new sheet into the destination workbook and then copying a range of cells to the clipboard and then pasting that range into a range on the inserted sheet. The last one can sometimes be fixed by saving the workbook in HTML format, closing Excel, starting Excel again and opening the HTML file and saving it back to .xls format. However, keep a copy of the original file as this can sometime lose some formatting in the file.

There may be other possible causes and my previous suggestion was a possible way to get around all of them. There may be better suggestions for each cause if it can be isolated.

[JohnBF]

Attached is some lightly tested code using Legare's approach of copying the entire book and deleteing the hidden sheets. It could probably be a bit more efficient. I added in some of those bells and whistles (they are easy).

[Frank Castle]

Legare,

Your suggestion about copying the file and then deleting the unwanted sheets worked out pretty well - it ends up being a little slower than copying the sheets, but it works for any amount of sheets, where copying the sheets did not. Your suggestion made perfect sense - I guess I just couldn't see it because I had become invested in fixing/solving the problem with my original method, as opposed to simply thinking in another direction.

Thanks again!

Frank

[Frank Castle]

John,

Thanks for that. I managed to muddle through it myself, but I see that your code ends up being pretty much the same as mine, except yours has some aspects mine doesn't (printing automatically, setting the status bar, pitching the temp file, etc.). I found some other threads that explained how to programmatically delete modules and userforms, so in the end, the file builds itself with the sheets it needs, in the appropriate order, then deletes all code from itself - since the "report" version of the file is placed on the network, I wanted to make sure nobody could open the file and accidentally run macros.

Thanks for all your help!

Frank