Howdy...I figure I'm either bumping up into some limitations in Excel, or (more likely) some limitations in my coding ability! Perhaps someone in the Lounge could help...
I have a spreadsheet with a tab called MasterDoc. The associated VBA Code has a loop that performs some calculations/updates to this MasterDoc worksheet, and then copies this MasterDoc worksheet into a new workbook, renames the tab in the new workbook, and then repeats this loop for the next store. It should loop through 12 stores.
Windows("Sale 2002 - v.1.10.xls").Activate
'Copy the Master worksheet to the interim Store Qty CommunicationMaster file
'for each store, then save that file with the final unique filename including Region, District and Store
Sheets("MasterDoc").Copy Before:=Workbooks("Store Qty CommunicationMaster.xls"). _
This works perfectly for the first 8 times through this iteration, but then fails on the 9th iteration...the error message is "Run-time Error '1004': Copy method of worksheet class failed." So when it crashes there are nine worksheets - Sheet1, plus my 8 copied/renamed worksheets, and it seems to fail when attempting to add the next worksheet. The Visual Basic Editor is pointing to the last line in the code sample shown above.
Is there some sort of limit in Excel to the number of worksheets I can add this way? More likely, I've just chosen some bad coding practices! Can anyone provide any suggestions?