ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   run-time error 1004 - VBA Gurus Please Help (https://www.excelbanter.com/excel-programming/375488-run-time-error-1004-vba-gurus-please-help.html)

SmartyPants
run-time error 1004 - VBA Gurus Please Help
 
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

Susan
run-time error 1004 - VBA Gurus Please Help
 
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

Charles Chickering
run-time error 1004 - VBA Gurus Please Help
 
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.
--
Charles Chickering

"A good example is twice the value of good advice."


"SmartyPants" wrote:

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

SmartyPants
run-time error 1004 - VBA Gurus Please Help
 

Susan wrote:
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

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

Susan
run-time error 1004 - VBA Gurus Please Help
 
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

SmartyPants
run-time error 1004 - VBA Gurus Please Help
 

Susan wrote:
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

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.

Charles Chickering
run-time error 1004 - VBA Gurus Please Help
 
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.
--
Charles Chickering

"A good example is twice the value of good advice."


"SmartyPants" wrote:


Susan wrote:
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

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

Susan
run-time error 1004 - VBA Gurus Please Help
 
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

SmartyPants
run-time error 1004 - VBA Gurus Please Help
 

Charles Chickering wrote:
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.
--
Charles Chickering

"A good example is twice the value of good advice."



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

Charles Chickering
run-time error 1004 - VBA Gurus Please Help
 
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

--
Charles Chickering

"A good example is twice the value of good advice."


"Charles Chickering" wrote:

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.
--
Charles Chickering

"A good example is twice the value of good advice."


"SmartyPants" wrote:


Susan wrote:
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

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

SmartyPants
run-time error 1004 - VBA Gurus Please Help
 

Charles Chickering wrote:
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

--
Charles Chickering


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

JLGWhiz
run-time error 1004 - VBA Gurus Please Help
 
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




"SmartyPants" wrote:

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

Charles Chickering
run-time error 1004 - VBA Gurus Please Help
 
JL, Why Activate the other sheet? It seems to me that it is unnecessary.
--
Charles Chickering

"A good example is twice the value of good advice."


"JLGWhiz" wrote:

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




"SmartyPants" wrote:

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


All times are GMT +1. The time now is 06:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com