"Run Time Error 1004 Application Defined or Object Defined Error."

  • Thread starter Thread starter BJC
  • Start date Start date
B

BJC

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
 
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
 
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.
 
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
 
cheers
Tom Ogilvy said:
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

(listed
Click to expand...
 
Back
Top