How to run same macro on 14 out 15 worksheets

  • Thread starter Thread starter Cheryl
  • Start date Start date
C

Cheryl

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!
 
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
 
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
 
--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
 
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
 
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.
 
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
 
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
 
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
 
Back
Top