Results 1 to 14 of 14
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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

  3. #3
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro stops after 76 worksheet copies? (Excel 2000)

    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

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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

  9. #9
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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.
    Legare Coleman

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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).
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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

  14. #14
    Lounger
    Join Date
    Jul 2004
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro stops after 76 worksheet copies? (Excel 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •