Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro in multiple worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make a macro to clear multiple cells from multiple worksheets? | Excel Worksheet Functions | |||
macro for multiple worksheets | Excel Programming | |||
Run a macro on multiple worksheets? | Excel Discussion (Misc queries) | |||
Use a macro on multiple Worksheets | Excel Discussion (Misc queries) | |||
Macro for multiple worksheets | Excel Programming |