Interesting, As I do have a blank workbook, that I thought I could use to
hold the macro for modifying the data as each workbook gets overwritten and
the macro contained therein with it..
Just having trouble with getting the macro to open the other workbooks and
running the macro against each one.
I used the recorder to quite a bit of the macro work for the data, But it
won't record the opening of another workbook.

Bing59


"Dave Peterson" wrote:

Create a dedicated workbook just for the macro code.

Then you can write the code against the active workbook.

And then open each workbook that needs to be modified manually, run the code,
close/save that workbook.

Repeat as required.

Or you could write the macro so that it opens each of the 12 workbooks,
reformats the sheets, saves/closes the workbook and opens the next one in your
list.

This would be close to the code in that macro workbook. You could plop a button
from the Forms toolbar onto a worksheet in that workbook and assign the macro to
that button.

Add a few instructions on that same sheet and you're almost done.

Option Explicit
Sub testme()
Dim wkbk As Workbook
Dim wks As Worksheet
Dim WkbkNames As Variant
Dim wCtr As Long
Dim myPath As String

myPath = "C:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

WkbkNames = Array("Book1.xls", _
"book2.xls", _
"book3.xls")

For wCtr = LBound(WkbkNames) To UBound(WkbkNames)
Set wkbk = Workbooks.Open(Filename:=myPath & WkbkNames(wCtr))

For Each wks In wkbk.Worksheets
'do the formatting to wks
Next wks

wkbk.Close savechanges:=True
Next wCtr

End Sub



Bing59 wrote:

I have 10 worbooks that contain 12 worksheets each. The worksheets all have
the same names. I have a macro written to that formats the data in each
worksheet. What I need to do is come up with a way to run the single macro
against each workbook indivdually.

All workbooks are in the same location.
My thinking is I should be able to make a call to something like *.xls in
the macro and have it run until all workbooks are updated.
One last thing, all workbooks get recreated once a week meaning if I store
the macro in the workbook it get destroyed along with the workbook, so I
store the macro in a blank workbook and I want that to auto Run the macro
when the workbook gets opened. Having trouble with Auto_Run in the blank
workbook.

I know it alot, But any help would be greatly appreciated.

Bing59

--

Dave Peterson
.