Formatting and sorting a specific and dynamic range regardless of sheet name

I need some help please. I have tried to record this through the Macro recorder which I know does not work well. I have also tried to get it to work through some other sample pieces of code but nothing get me all the way through the debug. This is what I'm trying to do.

I attached a mock up of an Invoice Aging Sheet for clarity. This is a very small sample. The ones that I'm working with can contain a couple hundred rows to many thousands but basically the initial file I pick up from an FTP directory is the same. I have to work with 100s of these every month and I would like to short cut a few formatting steps before I have to pass them along to the end users.

The first tab has data in A4 to G14 that I want to do a sort on the balance column in descending order then change range D5 to G14 to the accounting format. I was trying to select the cells in the macro so it would stop leaving the 2 last rows 15 and 16 intact. Since the last two rows are dynamic and will change on each sheet I was not sure how to approach this in a macro.

I also have had trouble with the tabs in the files as the tab always has different names so I want to do the process regardless of the tab name using the active sheet.

The second tab shows the result I'm looking for.

Hope this makes sense any help would be greatly appreciated as I have tried many times and can't seem to get it.

Hi dotsofcolour

My approach would be to keep a calling macro in a separate file, then use it to select the file you want to work with and process things that way.

Here is some code that should do the trick. Please note there is no error handling, so if the sheet format is not exact then trouble is brewing!
Run the StartHere macro, it will call the others as required.

Sub StartHere()
    Dim sfile As String
    Dim iAgain As Integer
    
Rerun:
    sfile = GetFile
    MsgBox sfile
    Call FormatFile(sfile)
    iAgain = MsgBox("Do you want to get another file?", vbYesNo, "RUN AGAIN???")
    If iAnswer = vbYes Then GoTo Rerun
End Sub

Function GetFile() As String
    
    Dim iRep As Integer
    Dim vFileToOpen As Variant
    
    Do Until iRep = vbYes
        MsgBox "Explorer will now open - Choose the file you want to get data from"
        vFileToOpen = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx,Excel Files (*.xls), *.xls", Title:="Please select a file")
        If vFileToOpen = False Then
            End ' They pressed Cancel
        Else
            iRep = MsgBox("ARE YOU SURE " & vFileToOpen & " IS THE CORRECT FILE ???", vbYesNo, "GET SOURCE FILE!")
        End If
    Loop
    
    GetFile = vFileToOpen
End Function

Sub FormatFile(sfile As String)
    Dim wbS As Workbook
    Dim wsS As Worksheet
    Dim lLR As Long
    Dim rng As Range
    
    Set wbS = Workbooks.Open(sfile, local:=True)
    
    For Each wsS In wbS.Sheets
        With wsS
        'find last row in data block
            lLR = .Range("A" & .Rows.Count).End(xlUp).Row
            Set rng = .Range("a4:g" & lLR)
        'sort by balance which is row 1, column 4 of the 'rng' block
            rng.Sort Key1:=rng(1, 4), Order1:=xlDescending, Header:=xlYes
        'format to currency
            Set rng = .Range("d5:g" & lLR)
            rng.NumberFormat = "$#,##0.00"
        End With
    Next wsS
    
    wbS.Close
    
    Set rng = Nothing
    Set wsS = Nothing
    Set wbS = Nothing
End Sub

Hope this solves, or points you in the right direction

Regards

Hi J450n

Should not

'format to currency
            Set rng = .Range("d5:g" & lLR)

be

'format to currency
            Set rng = .Range("d5:g" & lLR + 1)

Alf