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

[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

 

[Tom Ogilvy]

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

 

[BJC]

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]

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

 

[BJC]

cheers

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