Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Copy and Paste with Macro Between sheets | Excel Discussion (Misc queries) | |||
Compare two wk sheets with common data using copy paste macro | Excel Worksheet Functions | |||
Copy-Paste sheets - error in the dates | Excel Worksheet Functions | |||
Copy Sheets Macro | Excel Worksheet Functions | |||
Macro Error when moved Xcel Sheets from User's Home Dir to Networ | New Users to Excel |