Running Macro Across More Than One Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
You are never actually activating or selecting the next ws. Try
ws.activate
after your For Each statement.
James
 
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
 
Back
Top