Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet
Hi,
I have a routine where I copy a worksheet and place it in order in the workbook. In testing my workbook, it allowed me to copy 14 sheets then it stoped working and told me "Copy Method of object worksheet failed" on the copy after statement. I am unable to determine what is wrong. In debug the after sheet number is correct. Closing my workbook and reopening it cleared the problem but I can't have this happening with the user. If anyone could give some guidance as to how to trouble shoot this I would greatly appreciate it. Here is my code: Sub AddSheet(Newrow) 'Copy the worksheet Dim SheetName As String Dim ShNumber As Long Dim AfterShName As String Dim ShName As String Dim ShNotLogged As Boolean Dim TenName As String Dim msg As String 'check if this is the first tenant sheet If MainPagepg.Range("BD" & Newrow - 1) = "" Then ShNumber = Firstpg.Index Else 'get the name of the sheet before new tenant AfterShName = MainPagepg.Range("BD" & Newrow - 1).Value ShNumber = Sheets(AfterShName).Index End If TenName = MainPagepg.Range("F" & Newrow).Value 'get the new sheet name Call NewSheetName(SheetName, TenName) If SheetName = "" Then Exit Sub 'copy the sheet Call UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) 'name the sheet ActiveSheet.Name = (SheetName) Call ProtectSht(SheetName) Call ProtectWkbook 'add links from new sheet to main page Call AddNameMain(SheetName, Newrow) 'add links & formulas to new sheet from main page Call AddFormulaLinks(SheetName, Newrow) 'go to top of new page Sheets(SheetName).Application.Goto Reference:="R4C6" 'unload the form Unload frmStoreData 'check Master Page for sheets not logged Call SheetsNotLogged(ShNotLogged) End Sub -- Thanks for your help. Karen53 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
Copy hidden sheet and then make copy visible HELP? | Excel Programming | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
providing a sheet-copy event or copy CustomProperties | Excel Programming |