Run Macro on All Sheets Q

  • Thread starter Thread starter seanryanie
  • Start date Start date
S

seanryanie

I have a macro that runs a routine on the active sheet, but how would I create a macro that will run this macro on 'all' sheets expect sheet1 & sheet4?

I would prefer to create a new macro and call the original macro from within, so that I have the option to run individually
 
I have a macro that runs a routine on the active sheet,
but how would I create a macro that will run this macro
on 'all' sheets expect sheet1 & sheet4?
I would prefer to create a new macro and call the original
macro from within, so that I have the option to run individually
Click to expand...

Sub looper
Dim ws As Worksheet
For Each ws in Sheets
ws.Activate
call originalMacro
Next
End Sub
 
Here's one example (using SheetProtection) of how I normally handle
routines that I want this kind of flexibility with, that you may get
some ideas from for how to structure your project...

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: wksProtect Wks
End Sub

Sub Protect_AllSheets(Optional Wkb As Workbook)
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets: ResetProtection Wks: Next
Application.ScreenUpdating = True
End Sub

Sub Unprotect_AllSheets(Optional Wkb As Workbook)
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets: wksUnprotect Wks: Next
Application.ScreenUpdating = True
End Sub

...and the main process routine being called by the above...

Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if
there isn't one.
'
' Arguments: WksName [In] Optional. The name of the sheet to be
protected.
' Defaults to ActiveSheet.Name if missing.

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.VERSION) >= 10 Then
.Protect Password:=PWRD, _

DrawingObjects:=CBool(gvWksProtection(WksProtection.wpDrawingObjects)),
_
Contents:=CBool(gvWksProtection(WksProtection.wpContents)), _
Scenarios:=CBool(gvWksProtection(WksProtection.wpScenarios)), _

UserInterfaceOnly:=CBool(gvWksProtection(WksProtection.wpUserInterfaceOnly)),
_

AllowFiltering:=CBool(gvWksProtection(WksProtection.wpAllowFiltering)),
_

AllowFormattingColumns:=CBool(gvWksProtection(WksProtection.wpAllowFormatCols)),
_

AllowFormattingRows:=CBool(gvWksProtection(WksProtection.wpAllowFormatRows)),
_

AllowFormattingCells:=CBool(gvWksProtection(WksProtection.wpAllowFormatCells)),
_

AllowDeletingColumns:=CBool(gvWksProtection(WksProtection.wpAllowDeleteCols)),
_

AllowDeletingRows:=CBool(gvWksProtection(WksProtection.wpAllowDeleteRows)),
_

AllowInsertingColumns:=CBool(gvWksProtection(WksProtection.wpAllowInsertCols)),
_

AllowInsertingRows:=CBool(gvWksProtection(WksProtection.wpAllowInsertRows)),
_

AllowInsertingHyperlinks:=CBool(gvWksProtection(WksProtection.wpAllowInsertHLinks)),
_

AllowUsingPivotTables:=CBool(gvWksProtection(WksProtection.wpAllowPivotTables))
Else
.Protect Password:=PWRD, _

DrawingObjects:=CBool(gvWksProtection(WksProtection.wpDrawingObjects)),
_
Contents:=CBool(gvWksProtection(WksProtection.wpContents)), _
Scenarios:=CBool(gvWksProtection(WksProtection.wpScenarios)), _

UserInterfaceOnly:=CBool(gvWksProtection(WksProtection.wpUserInterfaceOnly))
End If
.EnableAutoFilter =
CBool(gvWksProtection(WksProtection.wpEnableAutoFilter))
.EnableOutlining =
CBool(gvWksProtection(WksProtection.wpEnableOutlining))
.EnableSelection =
CLng(gvWksProtection(WksProtection.wpEnableSelection))
End With 'Wks
End Sub 'wksProtect()

...which you can also call directly from any routine.

HTH

Note that the main routine uses Enum elements which I also can access
individually for each sheet by storing its protection settings in a
defined name as shown here...

Sub Set_EachWksProtection(Optional Wkb As Workbook)
' This applies sheet-specific protection as stored
' in the sheet's local scope defined name "uiProtect".
Dim vSettings, Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets
vSettings = Empty
On Error Resume Next
vSettings = Wks.Names("uiProtect").RefersTo
On Error GoTo 0
If Not (vSettings = Empty) Then
vSettings = Application.Evaluate("'" & Wks.name & "'!uiProtect")
gvWksProtection = Split(vSettings, ",")
wksProtect Wks
End If
Next 'wks
Application.ScreenUpdating = True: Set_DefaultWksProtection
End Sub

...and the supporting routine follows.

Sub Set_DefaultWksProtection()
' Use to reset to default params
gvWksProtection = Split(gsDEF_WKS_PROTECTION, ",")
End Sub

The declaration defs used...

Enum WksProtection
wpDrawingObjects '0
wpContents '1
wpScenarios '2
wpUserInterfaceOnly '3
wpAllowFiltering '4
wpAllowFormatCols '5
wpAllowFormatRows '6
wpAllowFormatCells '7
wpAllowDeleteCols '8
wpAllowDeleteRows '9
wpAllowInsertCols '10
wpAllowInsertRows '11
wpAllowInsertHLinks '12
wpAllowPivotTables '13
wpEnableAutoFilter '14
wpEnableOutlining '15
wpEnableSelection '16: 0=xlNoRestrictions; 1=xlUnlockedCells;
-4142=xlNoSelection
End Enum

Public gvWksProtection
Const gsDEF_WKS_PROTECTION As String =
"0,1,2,3,4,5,6,7,-8,-9,-10,-11,-12,-13,14,15,0"

...where the above Constant is the default used most commonly by my
multi-sheet projects. The application concept is simple: positive
numbers CBool as 'True', negative numbers as 'False'! The main routine
processes all protection options I'd likely use in a project.

The ResetProtection routine is used to re-apply non-persistent options
at startup. As you can see, it gives me the option to go with the
default settings or use sheet-specific settings when working with all
sheets in a workbook. It also gives me the option to edit the 'active'
settings stored in the gvWksProtection array and then apply it to a
specific sheet (or sheets) or just the active sheet 'on-the-fly'.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops! I forgot to include the following routine...

Sub wksUnprotect(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
Wks.Unprotect PWRD
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I find one more routine from this module...

Sub SetProtection_AllSheets(Optional Wkb As Workbook, _
Optional bApply As Boolean = True)
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets
If bApply Then ResetProtection Wks Else wksUnprotect Wks
Next 'Wks
Application.ScreenUpdating = True
End Sub

Note that I did not def the constant 'PWRD' in this modules
declarations because it normally exists in my m_OpenClose module as a
global, and so gets initiated by default when the project workbook
opens. (I use Auto_Open/Auto_Close routines)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Sub looper()
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet3" Then
ws.Activate
Call Original Macro
End If
Next
End Sub


Gord
 
Actually.., there were 2 more routines. This one lets you retrieve
protection settings for a specific sheet...

Function Get_WksProtectionSettings$(Optional Wkb As Workbook, Optional
Wks As Worksheet)
' This returns sheet-specific protection settings
' stored in the sheet's local scope defined name "uiProtect".
Dim vSettings
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
If Wks Is Nothing Then Set Wks = ActiveSheet
vSettings = Empty

On Error Resume Next
vSettings = Wks.Names("uiProtect").RefersTo
On Error GoTo 0
If Not (vSettings = Empty) Then _
vSettings = Application.Evaluate("'" & Wks.name & "'!uiProtect")
Get_WksProtectionSettings = vSettings
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Just another way to do the same thing without hard-coding the
sheetnames inside the routine...

Sub LoopSheets(ExcludedSheets$)
Dim wks As Worksheet
For each wks In ActiveWorkbook.Worksheets
If Not InStr(1, ExcludedSheets, wks.Name) > 0 Then _
wks.Activate: Call MainMacro
Next 'wks
End Sub

...where the sheets to be excluded from the process are a delimited
string that can be assigned 'on-the-fly'!

-OR-

Sub MainMacro(Wks As Worksheet)
With Wks
'//do stuff
End With 'Wks
End Sub

...then revise LoopSheets as follows...

Sub LoopSheets(ExcludedSheets$)
Dim wks As Worksheet
For each wks In ActiveWorkbook.Worksheets
If Not InStr(1, ExcludedSheets, wks.Name) > 0 Then _
Call MainMacro(wks)
Next 'wks
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Keeping in the same context, but adding more flexibility, I've adapted
the following code example from procedures in my code archives. It
demonstrates a means to include, or exclude, or both...

Sub ProcessSheets(WksList$, Process&, Optional Order& = 1)
' Processes listed sheets as specified by Process
' If Process=2 then the default Order is 1 if omitted
' WksList: String value of sheetnames
' Process: Long value; 0=exclude, 1=include, 2=both
' Order: Long value; 0=exclude followed by include
' 1=include followed by exclude

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Select Case Process
Case 0: Call Macro0(wks)
Case 1: Call Macro1(wks)
Case 2
Select Case Order
Case 0: Call Macro0(wks): Call Macro1(wks)
Case 1: Call Macro1(wks): Call Macro0(wks)
End Select 'Case Order
End Select 'Case Process
Next 'wks
End Sub

...where the called procedure accepts a ref for the sheet to act on.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top