Multiple Workbooks using same macro

[Bing59]

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]

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

 

[Bob Bridges]

Any chance you'd be interesting in doing this in VBScript instead? Assuming
the names of the 10 workbooks don't change from week to week, or that you can
calculate the name based on the date or can get part of the name from the
user, you can write a VBS program that a) fires up Excel, b) opens up each
workbook in turn and c) makes the format/whatever changes to each workbook.

VBS is a subset of VBA, but once you have it working you can run it just by
double-clicking it without even having to open Excel first. I use it a lot
for doing multiple-platform work, eg downloading a CSV from the mainframe or
pulling it from a server and then loading it into Excel, formatting it and
saving it as an XLS.

 

[Bing59]

Right now, I am interested in anything that will get this to work. The
workbook names do not change noe does the worksheets contained therein. I
currently only use 4 of the worksheets in a workbook but in the future that
could change also. So what I come up with would have to be scripted to allow
for future additions. Can you point me to a beginners reference manual that
I could use to get started.

Bing59

 

[Bing59]

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