How to run macro on selected sheets with one go?

H

Harshad

My workbook cointain 54 sheets. First four sheet named as "Main", "Content",
"Micro" and "Help".
From sheet five onward sheet is named as "Exp1", "Exp2", "Exp3"...... up to
"Exp50". I want to run three different macro NNA, NNB and NNC on each "Exp"
sheet.
Is their any code like below, so that i can run these three macro to "Exp1"
to "Exp50" in one go.
Sheets("Sheet Name").Activate
 
B

Bob Phillips

For Each ws In Activeworkbook.Worksheets

If ws.Name Like "Exp*" Then

Call NNA(ws)
Call NNB(ws)
CallNNC(ws)
End If
End With
 
H

Harshad

Daer Bob,

Not working. Some error

When i run following code the Call NNA is selects & massage look like:
Compile error: Wrong number of argument or invalid property assignment

The code is
Sub Test()
Dim WS As Worksheet
Dim WB As Workbook
Set WB = ActiveWorkbook
For Each WS In ActiveWorkbook.Worksheets
If WS.Name Like "Exp*" Then
Call NNA(WS)
Call NNB(WS)
Call NNC(WS)
End If
End With

End Sub

with regards,
Harsahd
 
B

Bob Phillips

You probably need to change NNA to look something like this

Sub NNA (sh As Worksheet)

With sh

End With
End Sub


instead of

Sub NNA ()

With Activesheet

End With
End Sub


and NNB and NNC.
 
H

Harshad

Daer Bob,
I'm very new to macro, i can't understand your code.
Whatever code i posted is i got from others.
So, pls write full code.

Pls i'm very new.

regards.
Harshad
 
H

Harshad

Dear Bob,
It's too long macro.

Sub NNA()
ActiveCell.Range("A1:IV65536").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveCell.Range("A1:O6").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(6, 0).Range("A1:B4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, 3).Range("A1:A4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, 2).Range("A1:A4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, 2).Range("A1:H4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(2, -6).Range("A1:O2").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=3
ActiveCell.Offset(2, 0).Range("A1:A21").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(20, 1).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(-20, 1).Range("A1:A13").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(1, 3).Range("A1:A12").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(-1, 4).Range("A1:A13").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(1, -3).Range("A1:A12").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(-1, 4).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(7, 0).Range("A1:A6").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(-7, 1).Range("A1:A7").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, 1).Range("A1:A13").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=9
ActiveCell.Offset(20, -10).Range("A1:M1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(1, -2).Range("A1:O4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=18
ActiveCell.Offset(4, 0).Range("A1:B11").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(1, 2).Range("A1:A2").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(5, 0).Range("A1:A2").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(4, 0).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(1, -2).Range("A1:O1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=9
ActiveCell.Offset(1, 0).Range("A1:O2").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(2, 1).Range("A1:A15").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(18, -1).Range("A1:O3").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=-9
ActiveCell.Offset(-18, 6).Range("A1:I18").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, -3).Range("A1:A18").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, 2).Range("A1:A18").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=-39
ActiveCell.Offset(-18, -3).Range("A1").Select
ActiveWindow.SmallScroll Down:=-6
ActiveCell.Offset(-24, 2).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(-9, -6).Range("A1").Select
ActiveCell.Offset(12, 6).Range("A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Offset(-12, -6).Range("A1").Select
ActiveCell.Offset(2, 1).Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Main!A1", TextToDisplay:="Main"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Contents!A1", TextToDisplay:="Content"
Selection.Font.Bold = True
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Font.Bold = True
ActiveCell.Offset(2, 1).Range("A1:E1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:[email protected]",
TextToDisplay:="(e-mail address removed)"
ActiveCell.Offset(-4, -2).Range("A1").Select
ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(11, 8).Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Solvent Properties'!A1", TextToDisplay:="Density"
Selection.Font.Bold = True
ActiveCell.Offset(-11, -8).Range("A1").Select
ActiveCell.Offset(4, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Help"
ActiveCell.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Help!A1", TextToDisplay:="Help"
Selection.Font.Bold = True
ActiveCell.Offset(-4, -1).Range("A1").Select
ActiveWindow.DisplayZeros = False
End Sub

with regards
Harshad
 
B

Bob Phillips

I wouldn't do it this way, but I haven't time to rewrite your code properly


Sub NNA(ByRef sh As Worksheet)
Dim ws As Worksheet

Set ws = Activesheet
sh.Activate

'your code

ws.Activate
End Sub
 
H

Harshad

Dear Bob,

When i prepare NNA code as you sugested, i don't see the macro NNA in tools>
macro > Macros, So how to run it?

With regards
Harshad
 
H

Harshad

Dear
I don't understand your meaning to say that it should be run from
anothermacro.

Harshad
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

specific macro on specific sheets 5
How to run specific macro on selected worksheet? 2
Macro to affect all sheets 3
Macro to Copy/Paste Data into one wksht, skipp one row before past 1
Run Macro on All Sheets Q 8
Macro To Delete/Add & Rename/Arrange Sheets 5
Macro to Combine Data from Multiple Sheets with Cell References 4
Run two macros for two different sheets 11

Top