Repeating macro procedure on several worksheets

  • Thread starter Thread starter Shandy720
  • Start date Start date
S

Shandy720

I have written the following macro which works fine but my problem i
how I can use this same macro on evry Sheet in my workbook.
The macro simply selects rows which have a certain cell value in colum
N and the pastes the entire row elsewhere. In my workbook there are 1
worksheets, I need to do this exact operation for 13 of thes
worksheets so is there a way of changing the macro so that it perform
the operation on these 13 worksheets.
Or can i change the range to help this.
Any help would be much apprecaited,


Sub MoTStrikeRate()
Dim rng As Range, cell As Range, sel As Range
Set rng = Intersect(Range("N:N"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Y" _
Or (cell.Value) = "y" Then
If sel Is Nothing Then
Set sel = cell
Else: Set sel = Union(sel, cell)
End If
End If
Next
On Error Resume Next
sel.EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Range("A210").Select
ActiveSheet.Paste
End Sub


Many thanks,
Andre
 
Here is an example.
Assume the two sheets you don't want to process are named "Master" and
"SUMMARY" then

Sub MoTStrikeRate()
Dim sh as Worksheet
Dim rng As Range, cell As Range, sel As Range
for each sh in Worksheets
if lcase(sh.name) <> "master" and lcase(sh.name) <> "summary" then
sh.Activate
Set rng = Intersect(Range("N:N"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Y" _
Or (cell.Value) = "y" Then
If sel Is Nothing Then
Set sel = cell
Else: Set sel = Union(sel, cell)
End If
End If
Next
On Error Resume Next
sel.EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Range("A210").Select
ActiveSheet.Paste
Next

End Sub
 
Try:

Sub AllMoTStrikeRate()
dim wks as worksheet

for each wks in activeworkbook.Sheets
If wks.name = "___'1 of 2 sheets to skip_" Then Goto NotThisSheet:
If wks.name = "__'2 of 2 sheets to skip__" Then Goto NotThisSheet:
wks.select
MoTStrikeRate
NotThisSheet:
Next wks
set wks = nothing
end Sub
 
Thanks for that, it works perfectly. Its very helpful to know too as
this is something I will have to do in the future with other macros,
i.e. making them applcable to only a selction of the worksheets.

Much appreciated,

:)
 
Back
Top