Run macro across all open workbooks????

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
102
Because of what I already read on here: I open excel then open then open my workbooks through excel.

I used the following code found here for the macro (assigned to ctnrl-k in personal workbook) but it seems to run twice on the first workbook and not at all on the second. (one sheet per book... don't know why it is running twice.

VBA Code:
Sub auctions()

     ' auctions Macro
     ' Keyboard Shortcut: Ctrl+k

     Dim wbkX As Workbook

     For Each wbkX In Application.Workbooks

           'macro code here'      
   
    Next
   
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Very difficult to tell as you haven't supplied the code. ;)
 
Upvote 0
I am guessing that you are not activating or explicitly referencing the workbooks within your code.
Looping like that does not actually activate each workbook. So, if you do not have any workbook references, all the loops will run on the active workbook.
 
Upvote 0
As Fluff asked, please post your code so we can see it.
Otherwise, we are just "guessing" as to what the problem may be.
 
Upvote 0
As Joe has said, if your code does not specify the workbook (& probably wont if it's recorded) then it will only work on the active workbook.
You can try
VBA Code:
       Sub auctions()

' auctions Macro
' Keyboard Shortcut: Ctrl+k

Dim wbkX As Workbook

For Each wbkX In Application.Workbooks
wbkX.Select
'macro code here' 

Next

End Sub
Although it's normally best to avoid selecting things.
 
Upvote 0
I guess I used code I don't fully understand LOL..

What is the best way to run a macro on 2 - 200 open workbooks. The original maco is just a recorded formating and is fine but I want to be able to run it without having to click on each and every workbook.
 
Upvote 0
Just select the workbook at the beginning of the loop, as per post#6
 
Upvote 0
OK, I see why you need my original macro code. Nothing so far works. The following code works as the author wrote it but when I substitute my code into it it just runs it twice on the same workbook.

his code

VBA Code:
Sub LoopEachOpenWorkbook()
Dim wb As Workbook

For Each wb In Application.Workbooks
  If wb.Name <> "PERSONAL.xlsb" Then
    
    'Add today's date to cell A1 of first worksheet in workbook
      wb.Worksheets(1).Range("A1") = Date
 
  End If
Next wb

End Sub


my code


Code:
Sub auctions()
'
' auctions Macro
'
' Keyboard Shortcut: Ctrl+k
    
    Dim wb As Workbook
        For Each wb In Application.Workbooks
        If wb.Name <> "PERSONAL.xlsb" Then
            
            Columns("A:A").Select
            Selection.delete Shift:=xlToLeft
            Columns("G:Q").Select
            Selection.delete Shift:=xlToLeft
            Columns("B:C").Select
            Selection.Cut
            Columns("H:H").Select
            Selection.Insert Shift:=xlToRight
            Columns("C:C").Select
            Selection.Cut
            Columns("J:J").Select
            Selection.Insert Shift:=xlToRight
            Columns("D:D").Select
            Selection.NumberFormat = "m/d/yy;@"
            Cells.Select
            With Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            Range("L12").Select
            Columns("F:F").ColumnWidth = 17.57
            Columns("B:B").ColumnWidth = 34.14
            Rows("1:1").Select
            Selection.delete Shift:=xlUp
            Range("B8").Select
            
        End If
        
        Next wb
  

End Sub
 
Upvote 0
How about
VBA Code:
Sub auctions()
'
' auctions Macro
'
' Keyboard Shortcut: Ctrl+k
    
   Dim wb As Workbook
   For Each wb In Application.Workbooks
      If wb.Name <> "PERSONAL.xlsb" Then
         With wb.Sheets(1)
            .Columns("A:A").Delete Shift:=xlToLeft
            .Columns("G:Q").Delete Shift:=xlToLeft
            .Columns("B:C").Cut
            .Columns("H:H").Insert Shift:=xlToRight
            .Columns("C:C").Cut
            .Columns("J:J").Insert Shift:=xlToRight
            .Columns("D:D").NumberFormat = "m/d/yy;@"
            With .Cells
               .HorizontalAlignment = xlLeft
               .VerticalAlignment = xlBottom
               .WrapText = False
               .Orientation = 0
               .AddIndent = False
               .IndentLevel = 0
               .ShrinkToFit = False
               .ReadingOrder = xlContext
               .MergeCells = False
            End With
            .Columns("F:F").ColumnWidth = 17.57
            .Columns("B:B").ColumnWidth = 34.14
            .Rows("1:1").Delete Shift:=xlUp
         End With
      End If
   Next wb
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top