Running 3 Macros on every file in a folder

[Julmust Jaeger]

Hello,

I have a three macros (see below) that I would like to run on every file in a folder (located at: H:\Accounts\C\2023\Dept).

All the files are in the .xlsx format.

Macro 1

Sub PI_Funds()
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim LR As Long, i As Long
Application.ScreenUpdating = False
With Sheets("Accounts")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        Sheets("Template").Copy after:=Sheets("Template")
        ActiveSheet.Name = .Range("B" & i).Value
    Next i
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub

Macro 2

Sub Replace_Formulas_with_Values()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Range("A:C").Value = ws.Range("A:C").Value
    Next ws
    
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub

Macro 3

'Delete Template, Reference, and Other Setup Worksheets and then Save
Sub Delete_Setup_Worksheets()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False


    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If (ws.Name = "Accounts") Or (ws.Name = "2023") Or (ws.Name = "Template") Or (ws.Name = "Reference") Or (ws.Name = "Specific_Accounts") Then
            Application.DisplayAlerts = False
            Sheets(ws.Name).Delete
            Application.DisplayAlerts = True
        End If
    Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveWorkbook.Save 'Save File
End Sub

I don't need to open the individual files, I would just like to have a way to run the code and then save the changes.

There are quite a few files (91) and some will generate around 20 worksheets using the copy/rename worksheet macro.

Thanks!

 

[johnnyL]

Not sure what you are asking for. If you are looking to have all 3 of those subs combined:

    Dim LR As Long, i As Long
    Dim ws As Worksheet
'
         Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
        Application.EnableEvents = False
      Application.ScreenUpdating = False
'
    With Sheets("Accounts")
        LR = .Range("B" & Rows.Count).End(xlUp).Row
'
        For i = 2 To LR
            Sheets("Template").Copy after:=Sheets("Template")
            ActiveSheet.Name = .Range("B" & i).Value
        Next i
    End With
'
    For Each ws In Worksheets
        ws.Range("A:C").Value = ws.Range("A:C").Value
    Next ws
'
    For Each ws In Worksheets
        If (ws.Name = "Accounts") Or (ws.Name = "2023") Or (ws.Name = "Template") Or (ws.Name = "Reference") Or (ws.Name = "Specific_Accounts") Then
            Application.DisplayAlerts = False
            Sheets(ws.Name).Delete
            Application.DisplayAlerts = True
        End If
    Next ws
'
    ActiveWorkbook.Save 'Save File
'
      Application.ScreenUpdating = True
        Application.EnableEvents = True
    Application.DisplayStatusBar = True
         Application.Calculation = xlCalculationAutomatic

 

[dmt32]

Hi,
untested but see if this reworking of your codes will do what you want

Place BOTH codes in a STANDARD module

Sub ProcessFiles()
    Dim wb              As Workbook
    Dim ws              As Worksheet
    Dim DeleteSheets    As Variant
    Dim FileName        As String
    Dim lr              As Long, i As Long
    
    'change folder path as required
    Const FolderPath   As String = "H:\Accounts\C\2023\Dept\"
    
    'list all sheets to be deleted
    DeleteSheets = Array("Accounts", "2023", "Template", "Reference", "Specific_Accounts")
    
    FileName = Dir(FolderPath & "\" & "*.xlsx", vbDirectory)
    
    EventsEnable False
    
    On Error GoTo myerror
    'Loop through all Files in path
    Do While Len(FileName) > 0
        
        Set wb = Workbooks.Open(FolderPath & FileName, 0, False)
        
        'PI_Funds
        Set ws = wb.Worksheets("Accounts")
        lr = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        
        For i = 2 To lr
            wb.Worksheets("Template").Copy after:=wb.Worksheets("Template")
            ActiveSheet.Name = ws.Range("B" & i).Value
        Next i
        
        Set ws = Nothing
        
        For Each ws In wb.Worksheets
            'Replace_Formulas_with_Values
            ws.Range("A:C").Value = ws.Range("A:C").Value
            'Delete_Setup_Worksheets
            If Not IsError(Application.Match(ws.Name, DeleteSheets, 0)) Then ws.Delete
        Next ws
        
        wb.Worksheets(1).Activate
        
        'close & save
        wb.Close True
        Set ws = Nothing
        Set wb = Nothing
        
        'next file
        FileName = Dir
        
    Loop
    
myerror:
    If Not wb Is Nothing Then wb.Close False
    EventsEnable True
    'inform user
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

Sub EventsEnable(ByVal State As Boolean)
    With Application
        .Calculation = IIf(State, xlCalculationAutomatic, xlCalculationManual)
        .ScreenUpdating = State
        .DisplayAlerts = State
        .DisplayStatusBar = State
        .EnableEvents = State
    End With
End Sub

I strongly suggest that you place only a couple of files in a TEMP folder & TEST the code before you apply to your main folder.

Hope Helpful

Dave

 

[Julmust Jaeger]

@dmt32

Hello!

That seems to almost do what I need it to, for some reason though it results in #REFS! for the copy worksheets I generate.

Each copy of the template sheet is correctly named, but something is creating problems for the cells that have formulas referencing the Accounts and Reference worksheets (basically when I run my macros I manually open each file, run the PI_Funds macro to copy templates and rename the created sheets (B1 populates with the worksheet name) and then I run Range("A:C").Value before to ensure that the cells populated by formulas are replaced with values before the worksheets used as references are deleted (and then I save).

 

[dmt32]

can you place a sample workbook with dummy data in a file sharing site like dropbox & provide a link to it?

Dave

 

[Julmust Jaeger]

Hey!

I think I may have figured it out, I simply split up the For - Next section into two:

For Each ws In wb.Worksheets
            'Replace_Formulas_with_Values
            ws.Range("A:C").Value = ws.Range("A:C").Value
        Next ws

        For Each ws In wb.Worksheets
            'Delete_Setup_Worksheets
            If Not IsError(Application.Match(ws.Name, DeleteSheets, 0)) Then ws.Delete
        Next ws

Is this is a reasonable solution or horribly inefficient? (As far as I can gather it works because the delete happens after the Range.Value).

 

[Julmust Jaeger]

Hmm, actually spoke too soon, for whatever, reason it's the value replacement and delete portion that seems to cause problems.

I suspect the issue is that all the copy sheets reference the same data worksheets ("2022","References", and "Specific_Accounts") as if I remove the replacement/deletion everything works well.

 For Each ws In wb.Worksheets
            'Replace_Formulas_with_Values
            ws.Range("A:C").Value = ws.Range("A:C").Value
            'Delete_Setup_Worksheets
            If Not IsError(Application.Match(ws.Name, DeleteSheets, 0)) Then ws.Delete
        Next ws

I will try to create some dummy sheets and post them in a sec.

 

[Julmust Jaeger]

@dmt32

Here's a link: Example Data JJ.xlsx

I couldn't include my template formulas for some reason but they are as follows

B1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

C7: =+INDEX(Specific_Accounts!$D$2:$D$500,MATCH(B1,Specific_Accounts!$B$2:$B$500,0),0)

B10: {=+IFERROR(INDEX(References!$A$1:$C$300,SMALL(IF(COUNTIF($B$1,References!$A$1:$A$300)*COUNTIF($A$9,References!$B$1:$B$300),ROW(References!$A$1:$C$300)),ROW(C1)),COLUMN(C1)),"")}
C10: =+SUMIFS('2023'!$N:$N,'2023'!$I:$I,$A$9,'2023'!$G:$G,$B$1,'2022'!$K:$K,B10)

Autofill to keep these formulas going until B14/C14

B16 & C16: same formula pattern as above

I was able to get the code to kind of work if I divide it into three parts and just call the macros in one additional macro (Step1 = just copy template and rename based on "Specific_Accounts" column B, Step 2: range.value for A:C in all worksheets, Step 3: delete worksheets on list). Although this is probably not the most efficient way to do it.

 

[dmt32]

Hey!

I think I may have figured it out, I simply split up the For - Next section into two:

If code worked ok in three parts previously, then this would be a suggestion to try & can only suggest that you keep playing with it if needed, and adjust to work with your real data.

Dave