Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

There is nothing wrong with the code presented except that you do NOT need
to select to do as desired. Probably something else wrong. Post ALL of your
code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is
nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

You can't select hidden sheets that's why it blow

See how I test if it is visible in this macro
http://www.rondebruin.nl/copy6.htm

You can also use xlSheetVisible instead of -1

Btw: In most cases you not have to select the sheets

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Ok, so by the sounds of it, I'd need to unhide all the worksheets first, and
then run the macro.
Is there a way to unhide a worksheet, run the macro, and then rehide it--
one hidden worksheet at a time, "automatically?"
Again.... thank you for your helps.


"Ron de Bruin" wrote:

You can't select hidden sheets that's why it blow

See how I test if it is visible in this macro
http://www.rondebruin.nl/copy6.htm

You can also use xlSheetVisible instead of -1

Btw: In most cases you not have to select the sheets

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Yes but what do you want to do ?
Maybe you not need to unhide the sheet first

This will work

Change the .PrintOut
line to your code

Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = CurVis
End With
Next sh
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Ok, so by the sounds of it, I'd need to unhide all the worksheets first, and
then run the macro.
Is there a way to unhide a worksheet, run the macro, and then rehide it--
one hidden worksheet at a time, "automatically?"
Again.... thank you for your helps.


"Ron de Bruin" wrote:

You can't select hidden sheets that's why it blow

See how I test if it is visible in this macro
http://www.rondebruin.nl/copy6.htm

You can also use xlSheetVisible instead of -1

Btw: In most cases you not have to select the sheets

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Ron,

What I'm doing is using Contexture's "delete_unused" (the one you referenced
in your article of the same discussion from your webpage- "Find Last row,
column, or last cell") macro to remove all excess, unused cells from all the
worksheets in all of our workbook files.
We have approximately 800 workbooks, and many are "bloated" with unused
cells, data, etc.... Many have hidden worksheets that we do not want deleted.
And on some of those hidden sheets there is the same problem-- unused cells--
which we want "cleaned up."
Thus far the macro works really great-- until I hit a book with hidden
sheets.
And so far I'm manually unhiding the worksheets, adding "hide" to the name
of the hidden worksheets (so I remember to rehide them), and then processing
the entire workbook.
As you can imagine, with some workbooks containing upwards of 50 to 100
worksheets, and as many as 10 to 15 hidden worksheets, this can get really
tedious and time consuming. I'm seeking to minimize the amount of manual
"labor" I need to perform. So far I've been able to reduce the "bloat" we're
experiencing by upwards of 70%. and I'm only 3% through the directory of
those 800 files.
Best.




"Ron de Bruin" wrote:

Yes but what do you want to do ?
Maybe you not need to unhide the sheet first

This will work

Change the .PrintOut
line to your code

Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = CurVis
End With
Next sh
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Ok, so by the sounds of it, I'd need to unhide all the worksheets first, and
then run the macro.
Is there a way to unhide a worksheet, run the macro, and then rehide it--
one hidden worksheet at a time, "automatically?"
Again.... thank you for your helps.


"Ron de Bruin" wrote:

You can't select hidden sheets that's why it blow

See how I test if it is visible in this macro
http://www.rondebruin.nl/copy6.htm

You can also use xlSheetVisible instead of -1

Btw: In most cases you not have to select the sheets

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Use the macro I posted

Sub Test_Macro()
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible

'your code

.Visible = CurVis
End With
Next sh
End Sub



Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Ron,

What I'm doing is using Contexture's "delete_unused" (the one you referenced
in your article of the same discussion from your webpage- "Find Last row,
column, or last cell") macro to remove all excess, unused cells from all the
worksheets in all of our workbook files.
We have approximately 800 workbooks, and many are "bloated" with unused
cells, data, etc.... Many have hidden worksheets that we do not want deleted.
And on some of those hidden sheets there is the same problem-- unused cells--
which we want "cleaned up."
Thus far the macro works really great-- until I hit a book with hidden
sheets.
And so far I'm manually unhiding the worksheets, adding "hide" to the name
of the hidden worksheets (so I remember to rehide them), and then processing
the entire workbook.
As you can imagine, with some workbooks containing upwards of 50 to 100
worksheets, and as many as 10 to 15 hidden worksheets, this can get really
tedious and time consuming. I'm seeking to minimize the amount of manual
"labor" I need to perform. So far I've been able to reduce the "bloat" we're
experiencing by upwards of 70%. and I'm only 3% through the directory of
those 800 files.
Best.




"Ron de Bruin" wrote:

Yes but what do you want to do ?
Maybe you not need to unhide the sheet first

This will work

Change the .PrintOut
line to your code

Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = CurVis
End With
Next sh
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Ok, so by the sounds of it, I'd need to unhide all the worksheets first, and
then run the macro.
Is there a way to unhide a worksheet, run the macro, and then rehide it--
one hidden worksheet at a time, "automatically?"
Again.... thank you for your helps.


"Ron de Bruin" wrote:

You can't select hidden sheets that's why it blow

See how I test if it is visible in this macro
http://www.rondebruin.nl/copy6.htm

You can also use xlSheetVisible instead of -1

Btw: In most cases you not have to select the sheets

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.

Hi Ron,
Please pardon my silence for this past week-- I've been out of town.
I trying to do this, I must've done something wrong.
So that I understand this, the CurVis is the variable of choice to change
all invisible worksheets to visible, using the .visible=xlSheetVisible. I
think that makes sense.
It bound up with the first invisible worksheet, so I'm thinking that I
placed my delete_unused code in the wrong location.
If I place the code where your comment says to place my code, would not the
process of "unhiding" the worksheets be incomplete?

I.e., the " .Visible = CurVis" is beneath my code; thus allowing my code to
run its course before it reaches that statement.
Or was it the goal to commit the CurVis to the .invisible, and then
re-assign the .visible back to the CurVis so as to not cause another problem?
I hope that I understand it clearly enough to be explaining it back clearly.
Again-- thank you for your helps.



"Ron de Bruin" wrote:

Use the macro I posted

Sub Test_Macro()
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible

'your code

.Visible = CurVis
End With
Next sh
End Sub



Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Ron,

What I'm doing is using Contexture's "delete_unused" (the one you referenced
in your article of the same discussion from your webpage- "Find Last row,
column, or last cell") macro to remove all excess, unused cells from all the
worksheets in all of our workbook files.
We have approximately 800 workbooks, and many are "bloated" with unused
cells, data, etc.... Many have hidden worksheets that we do not want deleted.
And on some of those hidden sheets there is the same problem-- unused cells--
which we want "cleaned up."
Thus far the macro works really great-- until I hit a book with hidden
sheets.
And so far I'm manually unhiding the worksheets, adding "hide" to the name
of the hidden worksheets (so I remember to rehide them), and then processing
the entire workbook.
As you can imagine, with some workbooks containing upwards of 50 to 100
worksheets, and as many as 10 to 15 hidden worksheets, this can get really
tedious and time consuming. I'm seeking to minimize the amount of manual
"labor" I need to perform. So far I've been able to reduce the "bloat" we're
experiencing by upwards of 70%. and I'm only 3% through the directory of
those 800 files.
Best.




"Ron de Bruin" wrote:

Yes but what do you want to do ?
Maybe you not need to unhide the sheet first

This will work

Change the .PrintOut
line to your code

Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = CurVis
End With
Next sh
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Ok, so by the sounds of it, I'd need to unhide all the worksheets first, and
then run the macro.
Is there a way to unhide a worksheet, run the macro, and then rehide it--
one hidden worksheet at a time, "automatically?"
Again.... thank you for your helps.


"Ron de Bruin" wrote:

You can't select hidden sheets that's why it blow

See how I test if it is visible in this macro
http://www.rondebruin.nl/copy6.htm

You can also use xlSheetVisible instead of -1

Btw: In most cases you not have to select the sheets

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hidden worksheets?
Actually, yes, there are. On most all of our workbook files that I'll be
using this on too.
And after I got an answer to this I was going to post on modifying hidden
worksheets with the macro.
How do I resolve that?
I.e., how can I have the macro process ALL ofthe worksheets, including the
hidden ones?
Don, the other macro works great without the "ALL worksheets" component.
It was after I'd added the ALL portion that I got the error.




"Ron de Bruin" wrote:

Do you have a hidden worksheet in your workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SteveDB1" wrote in message ...
Hi all.
Back on April 7, 2006 an individual calling themselves
Yardbird(http://www.microsoft.com/communities...5-233243fafda4)
posted on their desire to run a macro in a workbook to cover all the
worksheets in a workbook.
Gary Brown, Tom Ogilvy, and Bob Phillips responded with their resolution.
I'm now trying the code they used, and get a 1004 error. I did replace the
workbook.worksheets with activeworkbook.worksheets.

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Select
'code for my macro here....
Next Sh

It appears to get hung up on the 'sh.select', stating that "sh is nothing."

how do I resolve this to get it working?
(my macro code works great without this, and would like to get it to work
for all worksheets in a single stroke. )
Thank you.
Best.