Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Troubleshooting
The code below is designed to copy the activesheet, clear the
contents, and place it at the front of the workbook. It works well, but if I initiate the code a number of times without closing the workbook I get an error: Run-Time Error '1004' Copy method of worksheet class failed. Can any one figure out how to fix this problem, or explain why it happens? Also if anyone has any suggestions for how to make my code more efficient please let me know. Thank you. John Sub NewReport() ActiveSheet.Copy Befo=Sheets(1) ActiveSheet.Unprotect Application.ScreenUpdating = False Range("F3,G6,G8,B17:K31,U17:AD31,AE17:AX31,_ B35:AB54,AE34:BG54,B58:BG72,B88:BG149,I162:AD203,_ AK162:BE185,AK188:BD203,H205,C206:BF211").Select Selection.ClearContents Range("BK17:BK31").Value = 7 If Sheets(1).Range("AZ3").Value < "" And IsNumeric(Sheets(1).Range("AZ3").Value) = True Then ActiveSheet.Range("AZ3").Value = Sheets(2).Range("AZ3").Value + 1 Else ActiveSheet.Range("AZ3").Value = Application.Sheets.Count - 1 End If If Sheets(2).Range("AX6").Value < "" Then ActiveSheet.Range("AX6").Value = Sheets(2).Range("AX6").Value + 1 Else ActiveSheet.Range("AX6").Value = Date End If Rows("77:290").Select Selection.EntireRow.Hidden = True ActiveSheet.Shapes("Text Box 9").Visible = False ActiveSheet.Shapes("Button 5").Select Selection.Characters.Text = "Add Gridpaper" ActiveSheet.Shapes("Button 6").Select Selection.Characters.Text = "Add Time Log" ActiveSheet.Shapes("Button 8").Select Selection.Characters.Text = "Add Narrative" Range("BK34:BK42").Value = 0 Range("B17").Select ActiveSheet.Protect Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Troubleshooting
What version of Excel are you using ? How many is "a number of times" ?
What do the code names of the sheets look like ? Could they be something like Sheet1111111... ? You don't have to select things to work on them. So for example: Range("F3,G6,G8,B17:K31,U17:AD31, ... ,C206:BF211").Select Selection.ClearContents would be: Range("F3,G6,G8,B17:K31,U17:AD31, ... ,C206:BF211").ClearContents Regards Trevor "mastermind" wrote in message ups.com... The code below is designed to copy the activesheet, clear the contents, and place it at the front of the workbook. It works well, but if I initiate the code a number of times without closing the workbook I get an error: Run-Time Error '1004' Copy method of worksheet class failed. Can any one figure out how to fix this problem, or explain why it happens? Also if anyone has any suggestions for how to make my code more efficient please let me know. Thank you. John Sub NewReport() ActiveSheet.Copy Befo=Sheets(1) ActiveSheet.Unprotect Application.ScreenUpdating = False Range("F3,G6,G8,B17:K31,U17:AD31,AE17:AX31,_ B35:AB54,AE34:BG54,B58:BG72,B88:BG149,I162:AD203,_ AK162:BE185,AK188:BD203,H205,C206:BF211").Select Selection.ClearContents Range("BK17:BK31").Value = 7 If Sheets(1).Range("AZ3").Value < "" And IsNumeric(Sheets(1).Range("AZ3").Value) = True Then ActiveSheet.Range("AZ3").Value = Sheets(2).Range("AZ3").Value + 1 Else ActiveSheet.Range("AZ3").Value = Application.Sheets.Count - 1 End If If Sheets(2).Range("AX6").Value < "" Then ActiveSheet.Range("AX6").Value = Sheets(2).Range("AX6").Value + 1 Else ActiveSheet.Range("AX6").Value = Date End If Rows("77:290").Select Selection.EntireRow.Hidden = True ActiveSheet.Shapes("Text Box 9").Visible = False ActiveSheet.Shapes("Button 5").Select Selection.Characters.Text = "Add Gridpaper" ActiveSheet.Shapes("Button 6").Select Selection.Characters.Text = "Add Time Log" ActiveSheet.Shapes("Button 8").Select Selection.Characters.Text = "Add Narrative" Range("BK34:BK42").Value = 0 Range("B17").Select ActiveSheet.Protect Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Troubleshooting
hi,
just guessing. coping an entire sheet eats up the clipboard. and doing it several time is may be crashing the macro. I've had this happen to me in the past when i had the copy/paste commands in a loop. so i learned to avoid this by useing variables ie assign a range(or cell) to a variable and then have another range(or cell) as a variable equal the value of the first range thereby avoid the cllipboard altogeather. try this. after the copy add... Application.cutcopymode = false. in your code i don't set any where else you use the copy commands. if that don't work well enough then rethink your copy and only copy what you need instead of the entire sheet. or try it with variables. Regards FSt1 "mastermind" wrote: The code below is designed to copy the activesheet, clear the contents, and place it at the front of the workbook. It works well, but if I initiate the code a number of times without closing the workbook I get an error: Run-Time Error '1004' Copy method of worksheet class failed. Can any one figure out how to fix this problem, or explain why it happens? Also if anyone has any suggestions for how to make my code more efficient please let me know. Thank you. John Sub NewReport() ActiveSheet.Copy Befo=Sheets(1) ActiveSheet.Unprotect Application.ScreenUpdating = False Range("F3,G6,G8,B17:K31,U17:AD31,AE17:AX31,_ B35:AB54,AE34:BG54,B58:BG72,B88:BG149,I162:AD203,_ AK162:BE185,AK188:BD203,H205,C206:BF211").Select Selection.ClearContents Range("BK17:BK31").Value = 7 If Sheets(1).Range("AZ3").Value < "" And IsNumeric(Sheets(1).Range("AZ3").Value) = True Then ActiveSheet.Range("AZ3").Value = Sheets(2).Range("AZ3").Value + 1 Else ActiveSheet.Range("AZ3").Value = Application.Sheets.Count - 1 End If If Sheets(2).Range("AX6").Value < "" Then ActiveSheet.Range("AX6").Value = Sheets(2).Range("AX6").Value + 1 Else ActiveSheet.Range("AX6").Value = Date End If Rows("77:290").Select Selection.EntireRow.Hidden = True ActiveSheet.Shapes("Text Box 9").Visible = False ActiveSheet.Shapes("Button 5").Select Selection.Characters.Text = "Add Gridpaper" ActiveSheet.Shapes("Button 6").Select Selection.Characters.Text = "Add Time Log" ActiveSheet.Shapes("Button 8").Select Selection.Characters.Text = "Add Narrative" Range("BK34:BK42").Value = 0 Range("B17").Select ActiveSheet.Protect Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Troubleshooting | Excel Discussion (Misc queries) | |||
Function Troubleshooting | Excel Worksheet Functions | |||
Troubleshooting Spreadsheets | Excel Discussion (Misc queries) | |||
troubleshooting | Setting up and Configuration of Excel | |||
DDE Troubleshooting | Excel Programming |