Running Macro Across More Than One Sheet
 

I want to run the same macro (please see below) across the following
Worksheets:
"Bench", "Brake", "Indicator", "4", "Frame"

When I run this it works on the Worksheet "Bench" but it seems to run 5
times on that sheet without moving to the next sheet.

The code between the @@@@'s runs fine in isolation but I can't get the For
each and Next to work for me

Please can someone give me a steer on how to get the code right so the macro
runs once on each of the 5 specified sheets then stops.

Many thanks

Kewa
-----------------------
Sub Test()

Dim ws As Worksheet
For Each ws In Worksheets(Array("Bench", "Brake", "Indicator", "4",
"Frame"))

@@@@@@@@@@@@@@@@@@@

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="~", FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2),
Array(4, 9), Array(5, _
9), Array(6, 9), Array(7, 9), Array(8, 9), Array(9, 9), Array(10,
9), Array(11, 9), Array(12 _
, 9), Array(13, 9), Array(14, 9), Array(15, 9), Array(16, 9),
Array(17, 9), Array(18, 1), _
Array(19, 9), Array(20, 2), Array(21, 1), Array(22, 9), Array(23, 9))


Range("G1").Select
Range("G1").FormulaR1C1 = "=IF(RC6<TIME(19,0,0),RC1,RC1&"" +
""&RC2&"" ""&RC3&"" - ""&RC4)"

Set frng = Range("G1:G" & Range("f65536").End(xlUp).Row)
frng.FillDown

@@@@@@@@@@@@@@@@@@@

Next ws

End Sub

Running Macro Across More Than One Sheet
 

You are never actually activating or selecting the next ws. Try
ws.activate
after your For Each statement.
James

"nospaminlich" wrote in message
...
I want to run the same macro (please see below) across the following
Worksheets:
"Bench", "Brake", "Indicator", "4", "Frame"

When I run this it works on the Worksheet "Bench" but it seems to run 5
times on that sheet without moving to the next sheet.

The code between the @@@@'s runs fine in isolation but I can't get the For
each and Next to work for me

Please can someone give me a steer on how to get the code right so the
macro
runs once on each of the 5 specified sheets then stops.

Many thanks

Kewa
-----------------------
Sub Test()

Dim ws As Worksheet
For Each ws In Worksheets(Array("Bench", "Brake", "Indicator", "4",
"Frame"))

@@@@@@@@@@@@@@@@@@@

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="~", FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2),
Array(4, 9), Array(5, _
9), Array(6, 9), Array(7, 9), Array(8, 9), Array(9, 9), Array(10,
9), Array(11, 9), Array(12 _
, 9), Array(13, 9), Array(14, 9), Array(15, 9), Array(16, 9),
Array(17, 9), Array(18, 1), _
Array(19, 9), Array(20, 2), Array(21, 1), Array(22, 9), Array(23,
9))


Range("G1").Select
Range("G1").FormulaR1C1 = "=IF(RC6<TIME(19,0,0),RC1,RC1&"" +
""&RC2&"" ""&RC3&"" - ""&RC4)"

Set frng = Range("G1:G" & Range("f65536").End(xlUp).Row)
frng.FillDown

@@@@@@@@@@@@@@@@@@@

Next ws

End Sub

Running Macro Across More Than One Sheet
 

Doh! That's excellent. Thanks a lot - works a treat now!

Running Macro Across More Than One Sheet
 

Glad to help. By the way, I've never seen an array used with the
worksheets collection as you did. Very cool! So I definitely learned
something from you, as well! Regards, James
"nospaminlich" wrote in message
...
Doh! That's excellent. Thanks a lot - works a treat now!