|
"Run Time Error 1004 Application Defined or Object Defined Error."
Hi,
I get the following error when trying to add the code below to a custom button. I don't seem to get this error if i add to a drawing object though. Can anyone help? Am i doing something wrong? Error i receive is "Run Time Error 1004 Application Defined or Object Defined Error." Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count Sheets(i).Select Range("a2").Select Range("a1:z2000").Select 'Range("a1,a5000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1300:2500").Select Selection.Delete Shift:=xlUp Columns("Q:BG").Select Selection.Delete Shift:=xlToLeft Range("A1").Select Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete Sheets(1).Select Application.DisplayAlerts = True End Sub |
"Run Time Error 1004 Application Defined or Object Defined Error."
You can't use recorder style code in a worksheet module if you are going to
try to work on the activesheet and the activesheet will not be the sheet containing the code: Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count With Sheets(i) .Range("a1:z2000").Copy .Range("A1:Z2000).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows("1300:2500").Delete Shift:=xlUp .Columns("Q:BG").Delete Shift:=xlToLeft .Range("A1"). End With Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete On Error goto 0 Sheets(1).Select Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy BJC wrote in message ... Hi, I get the following error when trying to add the code below to a custom button. I don't seem to get this error if i add to a drawing object though. Can anyone help? Am i doing something wrong? Error i receive is "Run Time Error 1004 Application Defined or Object Defined Error." Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count Sheets(i).Select Range("a2").Select Range("a1:z2000").Select 'Range("a1,a5000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1300:2500").Select Selection.Delete Shift:=xlUp Columns("Q:BG").Select Selection.Delete Shift:=xlToLeft Range("A1").Select Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete Sheets(1).Select Application.DisplayAlerts = True End Sub |
"Run Time Error 1004 Application Defined or Object Defined Error."
Thanks for prompt Reply.
What would be the best way to handle the situation? I basically want a driver sheet at the beginning of the document that saves formula's on all the sheets as values, and deletes unwanted sheet (listed in code), in addition to the driver sheet. Thus leaving the formatted remaining code. I'd like to do this with a button in the driver sheet. Is this in any way possible? BJC. "Tom Ogilvy" wrote in message ... You can't use recorder style code in a worksheet module if you are going to try to work on the activesheet and the activesheet will not be the sheet containing the code: Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count With Sheets(i) .Range("a1:z2000").Copy .Range("A1:Z2000).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows("1300:2500").Delete Shift:=xlUp .Columns("Q:BG").Delete Shift:=xlToLeft .Range("A1"). End With Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete On Error goto 0 Sheets(1).Select Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy BJC wrote in message ... Hi, I get the following error when trying to add the code below to a custom button. I don't seem to get this error if i add to a drawing object though. Can anyone help? Am i doing something wrong? Error i receive is "Run Time Error 1004 Application Defined or Object Defined Error." Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count Sheets(i).Select Range("a2").Select Range("a1:z2000").Select 'Range("a1,a5000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1300:2500").Select Selection.Delete Shift:=xlUp Columns("Q:BG").Select Selection.Delete Shift:=xlToLeft Range("A1").Select Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete Sheets(1).Select Application.DisplayAlerts = True End Sub |
"Run Time Error 1004 Application Defined or Object Defined Error."
I did make a suggestion sohere is the code again. There were a couple of
typos in my original; this is tested and runs OK. Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count With Sheets(i) .Range("a1:z2000").Copy .Range("A1:Z2000").PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows("1300:2500").Delete Shift:=xlUp .Columns("Q:BG").Delete Shift:=xlToLeft End With Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete On Error GoTo 0 Sheets(1).Select Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy BJC wrote in message ... Thanks for prompt Reply. What would be the best way to handle the situation? I basically want a driver sheet at the beginning of the document that saves formula's on all the sheets as values, and deletes unwanted sheet (listed in code), in addition to the driver sheet. Thus leaving the formatted remaining code. I'd like to do this with a button in the driver sheet. Is this in any way possible? BJC. "Tom Ogilvy" wrote in message ... You can't use recorder style code in a worksheet module if you are going to try to work on the activesheet and the activesheet will not be the sheet containing the code: Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count With Sheets(i) .Range("a1:z2000").Copy .Range("A1:Z2000).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows("1300:2500").Delete Shift:=xlUp .Columns("Q:BG").Delete Shift:=xlToLeft .Range("A1"). End With Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete On Error goto 0 Sheets(1).Select Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy BJC wrote in message ... Hi, I get the following error when trying to add the code below to a custom button. I don't seem to get this error if i add to a drawing object though. Can anyone help? Am i doing something wrong? Error i receive is "Run Time Error 1004 Application Defined or Object Defined Error." Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count Sheets(i).Select Range("a2").Select Range("a1:z2000").Select 'Range("a1,a5000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1300:2500").Select Selection.Delete Shift:=xlUp Columns("Q:BG").Select Selection.Delete Shift:=xlToLeft Range("A1").Select Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete Sheets(1).Select Application.DisplayAlerts = True End Sub |
"Run Time Error 1004 Application Defined or Object Defined Error."
cheers
"Tom Ogilvy" wrote in message ... I did make a suggestion sohere is the code again. There were a couple of typos in my original; this is tested and runs OK. Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count With Sheets(i) .Range("a1:z2000").Copy .Range("A1:Z2000").PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows("1300:2500").Delete Shift:=xlUp .Columns("Q:BG").Delete Shift:=xlToLeft End With Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete On Error GoTo 0 Sheets(1).Select Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy BJC wrote in message ... Thanks for prompt Reply. What would be the best way to handle the situation? I basically want a driver sheet at the beginning of the document that saves formula's on all the sheets as values, and deletes unwanted sheet (listed in code), in addition to the driver sheet. Thus leaving the formatted remaining code. I'd like to do this with a button in the driver sheet. Is this in any way possible? BJC. "Tom Ogilvy" wrote in message ... You can't use recorder style code in a worksheet module if you are going to try to work on the activesheet and the activesheet will not be the sheet containing the code: Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count With Sheets(i) .Range("a1:z2000").Copy .Range("A1:Z2000).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows("1300:2500").Delete Shift:=xlUp .Columns("Q:BG").Delete Shift:=xlToLeft .Range("A1"). End With Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete On Error goto 0 Sheets(1).Select Application.DisplayAlerts = True End Sub -- Regards, Tom Ogilvy BJC wrote in message ... Hi, I get the following error when trying to add the code below to a custom button. I don't seem to get this error if i add to a drawing object though. Can anyone help? Am i doing something wrong? Error i receive is "Run Time Error 1004 Application Defined or Object Defined Error." Private Sub CommandButton1_Click() Count = Worksheets.Count For i = 2 To Count Sheets(i).Select Range("a2").Select Range("a1:z2000").Select 'Range("a1,a5000").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1300:2500").Select Selection.Delete Shift:=xlUp Columns("Q:BG").Select Selection.Delete Shift:=xlToLeft Range("A1").Select Next i Application.DisplayAlerts = False On Error Resume Next Sheets("trialbal").Delete Sheets("Input").Delete Sheets(1).Select Application.DisplayAlerts = True End Sub |
| All times are GMT +1. The time now is 01:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com