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

--
Shandy72
-----------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread.php?threadid=47474

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

--
Regards,
Tom Ogilvy


"Shandy720" wrote
in message ...

I have written the following macro which works fine but my problem is
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 column
N and the pastes the entire row elsewhere. In my workbook there are 15
worksheets, I need to do this exact operation for 13 of these
worksheets so is there a way of changing the macro so that it performs
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,
Andrew


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=474746

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

"Shandy720" wrote:


I have written the following macro which works fine but my problem is
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 column
N and the pastes the entire row elsewhere. In my workbook there are 15
worksheets, I need to do this exact operation for 13 of these
worksheets so is there a way of changing the macro so that it performs
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,
Andrew


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=474746

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,

:)


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=474746