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.

Please Login or Register  to view this content.

Hope this solves, or points you in the right direction

Regards

Hi J450n

Should not

Please Login or Register  to view this content.

be

Please Login or Register  to view this content.

Alf