I have a hundred workbooks, each with ten sheets. The sheets are identical in
structure and I want to create a macro that will run on each sheet of each
workbook. The problem comes with the fact that the worksheets aren't
consistently named across workbooks and so any sheet name references won't
work. My work around has been to write a general macro that will run on one
sheet and then select the sheets one by one and run it on each. I open each
workbook and run it sheet by sheet, which is time consuming. I'm wondering if
it's at all possible to create a macro that will run on all sheets in a
workbook regardless of their names.

Thanks for any help.

spence

I have a hundred workbooks, each with ten sheets. The sheets are identical
in
structure and I want to create a macro that will run on each sheet of each
workbook. The problem comes with the fact that the worksheets aren't
consistently named across workbooks and so any sheet name references won't
work. My work around has been to write a general macro that will run on
one
sheet and then select the sheets one by one and run it on each. I open
each
workbook and run it sheet by sheet, which is time consuming. I'm wondering
if
it's at all possible to create a macro that will run on all sheets in a
workbook regardless of their names.

You should be able to use this structure to run your code on each worksheet
in within the workbook your code is in...

Dim WS As Worksheet
For Each WS In Worksheets
'
' Your code goes here... reference current sheet being processed by WS
'
Next

Here is a quick example using this structure...

Dim Msg As String
Dim WS As Worksheet
For Each WS In Worksheets
Msg = Msg & WS.Name & vbCrLf
Next
MsgBox Msg

This will display all worksheet names from the workbook in a MessageBox.

Rick