| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run same macro on 14 out 15 worksheets
Excel 2003 (11.8307.8221) SP3
Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
Hi Cheryl
From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
Hi Jacob,
Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
--Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11. -- Select the correct workbook from the treeview.. VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook' Select 'This Workbook' Right click View Code If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Hi Jacob, Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
Thanks, Jacob! That worked. I didn't even have to reset the security. I
finally found (I can be so blind sometimes!) the proper "This Workbook" under the tree, copied the code there, and everything worked! Thanks again for your really fast and on target help! Cheryl "Jacob Skaria" wrote: --Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. -- Select the correct workbook from the treeview.. VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook' Select 'This Workbook' Right click View Code If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Hi Jacob, Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
For future reference (assuming versions of Excel *prior* to 2007), you can get to the "ThisWorkbook" code window of the workbook you are viewing by right clicking the small icon immediately to the left of the menu bar's File item and selecting View Code from the pop up menu that appears.
-- Rick (MVP - Excel) "Cheryl" wrote in message ... Thanks, Jacob! That worked. I didn't even have to reset the security. I finally found (I can be so blind sometimes!) the proper "This Workbook" under the tree, copied the code there, and everything worked! Thanks again for your really fast and on target help! Cheryl "Jacob Skaria" wrote: --Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. -- Select the correct workbook from the treeview.. VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook' Select 'This Workbook' Right click View Code If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Hi Jacob, Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
Hi Again!
Since posting this original message, my company has upgraded to office 2007 and my code doesn't seem to work anymore. Any suggestions? Cheryl "Rick Rothstein" wrote: For future reference (assuming versions of Excel *prior* to 2007), you can get to the "ThisWorkbook" code window of the workbook you are viewing by right clicking the small icon immediately to the left of the menu bar's File item and selecting View Code from the pop up menu that appears. -- Rick (MVP - Excel) "Cheryl" wrote in message ... Thanks, Jacob! That worked. I didn't even have to reset the security. I finally found (I can be so blind sometimes!) the proper "This Workbook" under the tree, copied the code there, and everything worked! Thanks again for your really fast and on target help! Cheryl "Jacob Skaria" wrote: --Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. -- Select the correct workbook from the treeview.. VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook' Select 'This Workbook' Right click View Code If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Hi Jacob, Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
Cheryl
Your code (macro) is simple and should work in 2007. The one thing that is different in 2007 is the process by which a file with code is opened with macros enabled. Have you placed the workbook in a trusted source folder or have you made the source folder a trusted source? Or does this sound like Greek to you? It was to me at first. Did you get any warning or any message upon opening the file that macros were disabled? Otto "Cheryl" wrote in message ... Hi Again! Since posting this original message, my company has upgraded to office 2007 and my code doesn't seem to work anymore. Any suggestions? Cheryl "Rick Rothstein" wrote: For future reference (assuming versions of Excel *prior* to 2007), you can get to the "ThisWorkbook" code window of the workbook you are viewing by right clicking the small icon immediately to the left of the menu bar's File item and selecting View Code from the pop up menu that appears. -- Rick (MVP - Excel) "Cheryl" wrote in message ... Thanks, Jacob! That worked. I didn't even have to reset the security. I finally found (I can be so blind sometimes!) the proper "This Workbook" under the tree, copied the code there, and everything worked! Thanks again for your really fast and on target help! Cheryl "Jacob Skaria" wrote: --Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. -- Select the correct workbook from the treeview.. VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook' Select 'This Workbook' Right click View Code If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Hi Jacob, Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to run same macro on 14 out 15 worksheets
Did you save the workbook in 2007 as *xlsm which is macro-enabled workbook.
If saved as *.xlsx Excel 2007 will simply delete the project. You would have received a notice about this when you tried to save. Gord Dibben MS Excel MVP On Tue, 8 Dec 2009 12:25:01 -0800, Cheryl wrote: Hi Again! Since posting this original message, my company has upgraded to office 2007 and my code doesn't seem to work anymore. Any suggestions? Cheryl "Rick Rothstein" wrote: For future reference (assuming versions of Excel *prior* to 2007), you can get to the "ThisWorkbook" code window of the workbook you are viewing by right clicking the small icon immediately to the left of the menu bar's File item and selecting View Code from the pop up menu that appears. -- Rick (MVP - Excel) "Cheryl" wrote in message ... Thanks, Jacob! That worked. I didn't even have to reset the security. I finally found (I can be so blind sometimes!) the proper "This Workbook" under the tree, copied the code there, and everything worked! Thanks again for your really fast and on target help! Cheryl "Jacob Skaria" wrote: --Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. -- Select the correct workbook from the treeview.. VBA Project(Your workbook name.xls)Microsoft Excel Objects'This Workbook' Select 'This Workbook' Right click View Code If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Hi Jacob, Thanks for you fast response. In the left treeview, I have 2 "ThisWorkbook"s, one under VBAProject (PERSONAL.XLS) and one under VBAProject (PERSONAL2.XLS). Both seem to have the same stuff in them. I double clicked the first "This Workbook" and copied the code you provided, did a save, then cycled through my worksheets. Only one of them (Sheet9, "SEP3 Support) actually has data in C3:C12, but nothing happened when I chose that sheet. Cheryl "Jacob Skaria" wrote: Hi Cheryl From workbook launch VBE using short-key Alt+F11. From the left treevew double click 'This Workbook '. Paste the below code and try selecting each sheet. Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < "Instructions" Then _ Range("C3:C12").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Cheryl" wrote: Excel 2003 (11.8307.8221) SP3 Windows XP I have a workbook that contains 15 worksheets. The first one is an "Instructions" page which contains links to the other 14. When any of the other 14 are selected, I would like to automatically clear C3 through C12. Is there an easy way to do this? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheets Macro | Excel Programming | |||
| New Worksheets Macro | Excel Worksheet Functions | |||
| How do i reuse a Excel macro on another worksheets, global macro? | Excel Programming | |||
| VBA / Macro for creating new worksheets and new columns from existing worksheets | Excel Programming | |||
| Problem in updating all worksheets of a workbook using a macro that calls another macro | Excel Programming | |||
Linear Mode
