Run a macro for all open workbooks

[smashclash]

I have a lot of files that need a macro run. Currently, I have to open a workbook, run the macro and then go to the next one.

I'm looking for the code to run the macro on all open workbooks. I'd rather not have the macro open each workbook, perform the changes, and then save. I simply want a macro that runs on one workbook, when its finished it moves to the next one until there are no workbooks left. Is there a simple code to do this? I tried doing some searches and due to my limited VB experience I had trouble deciphering which would work for my situation.

Not sure if this is needed or not but below is a copy of the macro I run.

Sub YTD()
'
' YTD Macro
' Macro recorded 3/10/2004 by CollaT
'

'
    
   
    
    
    
       
    Sheets("feb").Copy After:=Sheets(13)
    Sheets("feb (2)").Select
    Sheets("feb (2)").Name = "YTD"
    Range("A4:C4").Select
    ActiveCell.FormulaR1C1 = "YTD BALANCE"
    Range("A4:C4").Select
    ActiveCell.FormulaR1C1 = "2004 YTD BALANCE"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "Days in the Year"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "Hours in the Year"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "366"
    
    Range("E14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("E14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    
     Range("G14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("G14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    
    Range("K14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("K14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    
    Range("M14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("M14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    
    Range("Y14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("Y14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    
    Range("AA14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("AA14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
    
    Range("A1").Select
    
    
   
End Sub

 

[AgentSmith]

Try this:

Sub DoAll()
    Dim wbkX As Workbook
    
    For Each wbkX In Application.Workbooks
        wbkX.Activate
        YTD
        wbkX.Save
    Next
End Sub

Then run this macro instead of your YTD one.

 

[smashclash]

Try this:

Sub DoAll()
    Dim wbkX As Workbook
    
    For Each wbkX In Application.Workbooks
        wbkX.Activate
        YTD
        wbkX.Save
    Next
End Sub

Then run this macro instead of your YTD one.

Hmmmm, I'm getting a run-time error '9' - subscript out of range. The debugger then highlights the first line in my YTD macro. Also, I just want the macro to run on the workbook, not save the workbook. All I have to do is delete the wbkX.Save line to do this, correct?

 

[smashclash]

I just tried deleting the wbkX.activate line and the macro ran on the first file but attempted to run the macro twice for the same file. I got a 1004 error. It wouldn't allow the Feb sheet to be copied and renamed to YTD again. For some reason the next function isn't making it go to the next workbook. It's just looping on the same workbook.

 

[Joe4]

How were your workbooks opened? Were they all opened from the same instance of Excel? For example, after you opened the first one, did you open the next by going to File | Open from the same instance of Excel?

If each file was opened from a shortcut, or from something like Windows Explorer, you are actually opening different instances of Excel, and the macro will only run against files opened in the same instance.

A good way to check is to go to the Window drop down menu. At the bottom of the menu, does it list all the Excel files, or just one? It should list them all. If not, you have multiple instances and it won't work.

 

[smashclash]

Yes they were all open in the same instance of Excel. I'm stumped.

 

[Tom Urtis]

The reason why you are stumped is that Agent Smith's code was incomplete. Because it was activating other workbooks, but calling a macro (your YTD macro) to perform an operation on each active workbook, the macro's parent workbook needs to be qualified. This is what would work, using Agent Smith's workbook variable and macro name:

Sub DoAll()
Dim wbkX As Workbook
For Each wbkX In Application.Workbooks
wbkX.Activate 'really don't need to activate but we'll do it here instead of rewriting your YTD macro
Application.Run ThisWorkbook.Name & "!YTD"
Next wbkX
End Sub

 

[TryingToLearn]

Not sure if I'm heading in the right direction but:

Error is on this line:
Sheets("feb").Copy After:=Sheets(13)

Are there 13 worksheets in each workbook already?
If not and you want it placed after the last sheet, change it to:

Sheets("feb").Copy After:=Sheets(SheetCount)

And yes, just delete the save command line to leave them open and unsaved.


And just for kicks & giggles, I would replace the line YTD with

Call YTD

My limited experience says it makes a difference in the working environment (like which workbook you're working from) if you don't use the Call command. If anyone can verify & clarifiy this, I would appreciate it.

HTH

 

[TryingToLearn]

Tom:

It appears I was on the right track. Do you know If my statement about the Call function is correct?

 

[Tom Urtis]

TTL - -

You were on the right track if the top priority had been settled, that being the specifying of the parent workbook for a macro housed in another workbook running in an active workbook. The troubleshooting starts by recognizing the top of the object heierarchy and working down from there. We already settled the Application heierarchy of the same instance of Excel, so the Workbook object is the next possible culprit. If the ThisWorkbook workbook had been named, then your suggestion would be the next step.

Some time ago, I used Call in a macro and it did not do what I wanted in every case, but this was a monster macro. Still, monster or not, you always want the code to do what it is supposed to do. I don't remember the particulars, but I never used Call before that and never did again after. I either name the procedure as is (Macro1 or whatever), or, most often, I use the Run statement (example, Run "Macro1") because that has been foolproof for me so far.