Select multiple files in a folder and make changes on same columns

Question

Thank you for looking at my question.

I have 7 excel spreadsheets in a folder named MYWORKSHEETS

I need to make the same changes on each spread sheet and would like to select all the files in the folder and make the changes all at once in a macro.

In column E, I have a total that I need to change to Dollar Amount with no $ dollar sign and paste it back  as values so that it removes the formula and just keeps the values)

Delete Column A, J, L, M

Thank you so much

Nana from NC

Answer 1

Here is a macro. You may want to test it on a copy of the folder first.

Sub ProcessWorkbooks()

    ' Change path as needed, but keep \ at the end

    Const strPath = "C:\MYWORKSHEETS"

    Dim strFile As String

    Dim wbk As Workbook

    Dim wsh As Worksheet

    Application.ScreenUpdating = False

    strFile = Dir(strPath & "*.xls*")

    Do While strFile <> ""

        Set wbk = Workbooks.Open(strPath & strFile)

        Set wsh = wbk.Worksheets(1)

        With wsh.Range("E:E")

            .NumberFormat = "#,##0.00"

            .Value = .Value

        End With

        wsh.Range("A:A,J:J,L:M").Delete Shift:=xlToLeft

        wbk.Close SaveChanges:=True

        strFile = Dir

    Loop

    Application.ScreenUpdating = True

End Sub