running macro on multiple workbooks
running macro on multiple workbooks
(OP)
I have many situations where I'm performing the same macro on 70 or 80 workbooks that are open at once. I have no problems writing the macros to do what I want in each individual workbook, but what I'd like to be able to do is put a loop around my macro so that it would automatically churn through every open workbook and run the macro on it. Does anyone know how to tell VBA that I want to do this?
Thanks!
Thanks!





RE: running macro on multiple workbooks
RE: running macro on multiple workbooks
RE: running macro on multiple workbooks
RE: running macro on multiple workbooks
For instance, during the first loop through, the macro will open the workbook at index number 1, and modify it. Then, it will close that workbook and increment the loop index number. This means that there are now 19 available open workbooks that are indexed and the loop counter is at 2. The next loop, it opens and modifies index #2, deletes that file bringing the number of open workbooks to 18, and increments the counter to 3.
At about the halfway point, it will be trying to open index number 11, but it will have closed 10 workbooks, so there is no index number 11 available, and the subscript goes out of range and everything comes to screeching halt.
I'm thinking of just running two loops. The first does the modification on all of the files, and the second closes all of the files. I just wonder if there's a more efficient way to do it.
RE: running macro on multiple workbooks
Thanks, all!
RE: running macro on multiple workbooks
if the workbooks are new workbooks with same formatting/features, then perhaps creating one workbook and create new workbooks by copying and naming the file differently.
good luck!
-pmover
RE: running macro on multiple workbooks
Sub getAllOpenWBNames()
Dim wb As Workbook
For Each wb In Application.Workbooks
Debug.Print wb.Name
Next wb
End Sub
RE: running macro on multiple workbooks
'Do your stuff here
ActiveWorkbook.close
Wend
RE: running macro on multiple workbooks