If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy sheets macro error
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 befo=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 befo=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 |
#2
|
|||
|
|||
Copy sheets macro error
Perhaps...
http://support.microsoft.com/default...84&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... -- HTH... Jim Thomlinson "Horatio J. Bilge, Jr." wrote: 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 befo=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 befo=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 |
#3
|
|||
|
|||
Copy sheets macro error
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 "Jim Thomlinson" wrote: Perhaps... http://support.microsoft.com/default...84&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... -- HTH... Jim Thomlinson "Horatio J. Bilge, Jr." wrote: 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 befo=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 befo=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 |
#4
|
|||
|
|||
Copy sheets macro error
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 "Horatio J. Bilge, Jr." wrote: 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 "Jim Thomlinson" wrote: Perhaps... http://support.microsoft.com/default...84&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... -- HTH... Jim Thomlinson "Horatio J. Bilge, Jr." wrote: 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 befo=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 befo=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 |
Thread Tools | |
Display Modes | |
|
|