What and where put code to run macro in all sheets (in opened workbook)

Vladj

New Member
Joined
Dec 4, 2018
Messages
2
My problem is, where and what to put inside my macro to run macro in all sheets in currently opened workbook. I tried several things but without success.


Code:
[COLOR=#333333]
Sub LoopAllExcelFilesInFolder()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & ""
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    Range("J33").Formula = "=FIXED(SUM(L33:T33),2)"
    Range("J33", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
         
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Riješeno!!!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub[/COLOR]
Thanks in advance. :)
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
If I understand you correctly you could try this

Declare a variable, say, sht as Worksheet.

Then where you have

Range("J33").Formula = "=FIXED(SUM(L33:T33),2)"
Range("J33", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown


Change this to

For Each sht in Worksheets
sht.Range("J33").Formula = "=FIXED(SUM(L33:T33),2)"
sht.Range("J33", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Next sht
 

Vladj

New Member
Joined
Dec 4, 2018
Messages
2
OMG... I tried few combinations like yours and it was unsuccessful. I can't believe that now working everything fine.
Probably I miss some letter...
Anyway, thank you very much. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

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
Top