Copy sheets macro error

[Horatio J. Bilge, Jr.]

I wrote some code to make copies of a worksheet when opening a file. The
number of copies is based on user input on a userform. I'm now trying to do
the same thing in a different workbook, but I'm getting unexpected errors.

The way it is supposed to work: When opening the workbook, the user enters a
number on a userform. When they click submit on the userform, the number is
passed to the CopySheets sub (code below). The code makes copies of the
Original sheet, lists the names of the copies on the Master sheet, hides the
Original sheet, and then Saves the file using GetSaveAsFilename method.

Here is what is actually happening: The sheets copy correctly, the file is
saved, and then I get the error "Method 'Copy' of object '_Worksheet'
failed." When I click Debug, the line highlighted is:
WS.Copy before:=Worksheets("MyChart").

Sub CopySheets(ByVal Copies As Integer)
Dim Original As Worksheet
Dim Master As Worksheet
Dim i As Integer
Dim fSaveName As Variant
Set Original = ThisWorkbook.Worksheets("Original")
Set Master = ThisWorkbook.Worksheets("Master")

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

ThisWorkbook.Unprotect
Master.Unprotect
For i = 1 To Copies
Original.Copy before:=Worksheets("MyChart")
ActiveSheet.Name = "Copy" & i
Master.Range("A" & i) = "Copy" & i
Next i
Original.Visible = xlSheetVeryHidden
Master.Protect
ThisWorkbook.Protect

WhereToSave:
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName <> False

Application.DisplayAlerts = False
ThisWorkbook.SaveAs fSaveName
Application.DisplayAlerts = True

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Jim Thomlinson]

Perhaps...

http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw

Not sure from your description. Does it work sometimes? Does it make copies
but not enough? if so then the above article may be the culpret. In any case
you will want to read it as it does apply to what you are up to...

 

[Horatio J. Bilge, Jr.]

I have two workbooks with the code. In the first workbook, it works
perfectly. The second workbook is causing the errors. The structure of the
workbooks is very similar. The sheet being copied is somewhat different
(different layout, and some different named ranges); the code in one of the
separate modules is different. What seems really strange to me is that the
CopySheets code actually executes perfectly. It is only AFTER the sheets are
copied and the file saved that the error pops up. If I click End instead of
Debug, I can use the workbook as expected.

~ Horatio

 

[Horatio J. Bilge, Jr.]

Well, I tracked down the problem. It was a stupid mistake...
I was calling the CopySheets macro twice. The first time it worked
perfectly, and then the second time it gave an error. I deleted the offending
line of code, and it works great now.

~ Horatio