run-time error 1004 - VBA Gurus Please Help

S

SmartyPants

I am trying to set up a button that clears cells over several
worksheets.

But when it tries to switch to another worksheet, I get this error
-1004.

It say "select method of range class failed"

How can I correct this problem?

Here is the code I am using:

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents
Sheets("Hours").Select
Range("D7:D12").Select (This is where the error occurs)
Selection.ClearContents
Range("F7:F12").Select
Selection.ClearContents
Range("G13:K13").Select
Selection.ClearContents
Range("M13:p13").Select
Selection.ClearContents
Range("D14:D19").Select
Selection.ClearContents
Range("F14:F19").Select
Selection.ClearContents
Range("G20:K20").Select
Selection.ClearContents
Range("M20:p20").Select
Selection.ClearContents
Range("D21:D26").Select
Selection.ClearContents
Range("F21:F26").Select
Selection.ClearContents
Range("G27:K27").Select
Selection.ClearContents
Range("M27:p27").Select
Selection.ClearContents
Range("D28:D33").Select
Selection.ClearContents
Range("F28:F33").Select
Selection.ClearContents
Range("G34:K34").Select
Selection.ClearContents
Range("M34:p34").Select
Selection.ClearContents
Range("C35:F40").Select
Selection.ClearContents
Range("G41:K41").Select
Selection.ClearContents
Range("M41:p41").Select
Selection.ClearContents
Range("C28:F33").Select
Selection.ClearContents
Range("C21:F26").Select
Selection.ClearContents
Range("C14:F19").Select
Selection.ClearContents
Range("C7:F12").Select
Selection.ClearContents
Range("Q7:U41").Select
Selection.ClearContents
Range("A46:X58").Select
Selection.ClearContents
Sheets("Salary Absentees").Select
Range("A5:C24").Select
Selection.ClearContents
Sheets("No Tires").Select
Range("B4:L40").Select
Selection.ClearContents
Sheets("Production").Select
Range("N3").Select
 
S

Susan

the gurus just helped me with "select".
you can measurably shorten your code by using

Sheets("Production").Range("D4").ClearContents
(one line instead of three).

OR
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
'continue
End With

With xxxxxNextSheet
End With

as for why your button isn't working, where is the button located?
toolbar? activex control? userform?
susan
 
C

Charles Chickering

Selection is not the best approach here. Try this:
With Sheets("Production")
.Range("D4").ClearContents
.Range(...).ClearContents
End With
With Sheets("Hours")
.Range("M27:p27").ClearContents
End With

That should run much faster.
 
S

SmartyPants

Susan said:
the gurus just helped me with "select".
you can measurably shorten your code by using

Sheets("Production").Range("D4").ClearContents
(one line instead of three).

OR
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
'continue
End With

With xxxxxNextSheet
End With

as for why your button isn't working, where is the button located?
toolbar? activex control? userform?
susan
Click to expand...

The button is located on the actual sheet(production).
 
S

Susan

The button is located on the actual sheet(production).

why don't you try changing that to a toolbar button or an autoshape
with the macro attached to it?
it could be (although i don't know a lot about activex) that since it's
attached to that first sheet, it won't let you move to another sheet.
susan
 
S

SmartyPants

Susan said:
why don't you try changing that to a toolbar button or an autoshape
with the macro attached to it?
it could be (although i don't know a lot about activex) that since it's
attached to that first sheet, it won't let you move to another sheet.
susan
Click to expand...

I have a "Print-All" button set up the same way and it works fine.

It's got to be the difference between printing and actively modifying a
cell.
 
C

Charles Chickering

I'm guessing that the code is in the "Production" sheet code area. if that is
the case then Susan is correct, you cannot select another sheet from there.
You can however create a macro in a separate module and call that macro from
your button.
 
S

Susan

Charles -
that's why you're a guru & i'm not! i didn't even think of
WHERE the code was stored! (i was on the right track
but didn't know why - ha ha).
:^D
susan
 
S

SmartyPants

Charles said:
I'm guessing that the code is in the "Production" sheet code area. if that is
the case then Susan is correct, you cannot select another sheet from there.
You can however create a macro in a separate module and call that macro from
your button.
Click to expand...

Sounds right... how do I call a macro from a button?
 
C

Charles Chickering

I gave you wrong information, you can modify another sheet from the sheet
code, however the problem is when you state Range("D2").Select from the sheet
code it thinks you mean sheets("Production").Range("D2") you need to specify
the parent object in order to fix this error.
Either:
Sheets("hours").Range("D2").Select
or:
With Sheets("hours")
.Range("D2").Select
End with
 
S

SmartyPants

Charles said:
I gave you wrong information, you can modify another sheet from the sheet
code, however the problem is when you state Range("D2").Select from the sheet
code it thinks you mean sheets("Production").Range("D2") you need to specify
the parent object in order to fix this error.
Either:
Sheets("hours").Range("D2").Select
or:
With Sheets("hours")
.Range("D2").Select
End with
Click to expand...

SUCCESS!!! Thank You!

This is the new code:

Private Sub CommandButton4_Click()

Sheets("Production").Select
Range("D4").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents
Range("H4").Select
Selection.ClearContents
Range("D7:D10").Select
Selection.ClearContents
Range("F7:F10").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("N3").Select
Selection.ClearContents
Range("L6:Q13").Select
Selection.ClearContents
Range("D17:D24").Select
Selection.ClearContents
Range("F17:F24").Select
Selection.ClearContents
Range("H17:H24").Select
Selection.ClearContents
Range("M17:Q24").Select
Selection.ClearContents
Range("L29:Q38").Select
Selection.ClearContents
Range("B34:B38").Select
Selection.ClearContents
Range("E34:I38").Select
Selection.ClearContents
Range("B43:N51").Select
Selection.ClearContents

Sheets("Hours").Range("G13:K13").ClearContents

Sheets("Hours").Range("M13:p13").ClearContents

Sheets("Hours").Range("G20:K20").ClearContents

Sheets("Hours").Range("M20:p20").ClearContents

Sheets("Hours").Range("G27:K27").ClearContents

Sheets("Hours").Range("M27:p27").ClearContents

Sheets("Hours").Range("G34:K34").ClearContents

Sheets("Hours").Range("M34:p34").ClearContents

Sheets("Hours").Range("C35:F40").ClearContents

Sheets("Hours").Range("G41:K41").ClearContents

Sheets("Hours").Range("M41:p41").ClearContents

Sheets("Hours").Range("C28:F33").ClearContents

Sheets("Hours").Range("C21:F26").ClearContents

Sheets("Hours").Range("C14:F19").ClearContents

Sheets("Hours").Range("C7:F12").ClearContents

Sheets("Hours").Range("Q7:U41").ClearContents

Sheets("Hours").Range("A46:X58").ClearContents

Sheets("Salary Absentees").Range("A5:C24").ClearContents

Sheets("No Tires").Range("B4:L40").ClearContents

Sheets("Production").Range("N3").Select


End Sub
 
J

JLGWhiz

If I was going to do what you are trying to do, I would assign a macro to the
button like this:
Sub commandbutton1_click()
clrContents
End Sub

Then in the general code module I would put the code you have written with
the name clrContents:

Sub clrContents()
With Sheets("Production")
.Range("D4").ClearContents
.Range("F4").ClearContents
(etc.)
End With
Sheets("Hours").Activate
With Sheets("Hours")
.(etc)
End Eith
(etc.)
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-Time Error '1004' 13
Run Time Error 1004: Please help 3
Run Time Error 1004: Please Help 3
run-time error 1004 1
Run-time Error '1004' 3
Macro Run-time Error 1004 Application Defined or Object Defined Error 6
Macro Issue - Run time error 1004 1
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0

Top