Paste range of formulas to a sheet using Personal Macro Workbook

Why don't you loop through all the excel files automatically?
Just put them all in a folder and use this code.

I allredy posted this another thread today, and he got it working.
Just change the folder where all you excel files is, and then change the Mymacro at the bottom to make the changes you need.
This code will then loop through all the files, make the changes and save them.

Public Function IsFileOpen(strFileName As String) As Boolean
    
    On Error Resume Next 'Ignore any errors (i.e. if workbook is not open)
    
        Set wrkFileName = Workbooks(strFileName)
        
            If wrkFileName Is Nothing Then
                IsFileOpen = False
            Else
                IsFileOpen = True
            End If
            
    On Error GoTo 0 'Nullify above error handler
    
End Function
Sub Macro1()

    Dim strDir As String, _
        strFileType As String
    Dim objFSO As Object, _
        objFolder As Object, _
        objFile As Object
    Dim intCounter As Integer
        
    strDir = "C:\" 'Change to suit
    strFileType = "xl*" 'Shouldn't need to, but change to suit if required
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strDir)
    
    Application.ScreenUpdating = False
        
    For Each objFile In objFolder.Files
        'If the file in the 'strDir' directory is not this workbook, then...
        If objFile.Name <> ThisWorkbook.Name Then
            If objFile.Name Like "*." & strFileType Then
                '...check to see if it's open.  If it is...
                If IsFileOpen(objFile.Name) = True Then
                    '...run the 'MyMacro' passing the active workbook variable with it and _
                    increment the counter.
                     Call MyMacro(objFile.Name)
                     intCounter = intCounter + 1
                'Else, _
                1. Open the file, _
                2. Run the 'MyMacro' passing the active workbook variable with it, _
                3. Save the changes and close the file, and _
                4. Increment the counter.
                Else
                     Workbooks.Open (strDir & "\" & objFile.Name), UpdateLinks:=False
                     Call MyMacro(objFile.Name)
                     Workbooks(objFile.Name).Close SaveChanges:=True
                     intCounter = intCounter + 1
                End If
            End If
        End If
        'Release memory
        Set objFSO = Nothing
        Set objFolder = Nothing
        Set objFile = Nothing
    Next objFile
    
    Application.ScreenUpdating = True
    
    Select Case intCounter
        Case Is = 0
            MsgBox "There were no """ & strFileType & """ file types in the """ & strDir & """ directory for the desired macro to be run on.", vbExclamation, "Data Execution Editor"
        Case Is = 1
            MsgBox "The desired macro has been run on the only """ & strFileType & """ file in the """ & strDir & """ directory.", vbInformation, "Data Execution Editor"
        Case Is > 1
            MsgBox "The desired macro has now been run on the " & intCounter & " files in the """ & strDir & """ directory.", vbInformation, "Data Execution Editor"
    End Select
    
End Sub

Sub MyMacro(strDesiredWkb As String)

'Add the code what you want changed in the workbooks here

End Sub

Unfortunately, I have to do it by the method that I described for several reasons. One of which is I have to decide which files I want to run the macro in, as sometimes I may want to use the old formulas. Also, I share my personal macro file with a colleague as we both use the same macros for our work.