Excel Automation: Updating Pivot Tables, Charts, and Tables

At my work, we have an Excel spreadsheet that gets updated monthly and sent out to the company. This spreadsheet contains upwards of 5 years of data, with ~500-600 orders per month. Every month, the newest month's raw data is added to the spreadsheet, then various Pivot Tables in the workbook are refreshed, a table full of formulas is expanded into a new column so that the formulas can run on the new data, a different worksheet of formulas is expanded for the new data, and finally a set of "dashboard graphs" is updated based on these final formulas to reflect the new data. All of this is currently done manually, and depending on the month it can be a many-hour process.

I've been tasked with automating this process so that, instead of monthly, the process can be run whenever somebody wants it run. I currently have a macro to import the raw data directly from the database we use. Now I'm struggling with updating the formula tables, pivot tables, and charts to automatically incorporate the new data. All the macros that I'm capable of writing or recording at some point require knowing exactly what cells formulas are being applied to, and since the spreadsheet is ever-expanding that column will change every month.

I'm sorry I can't be more specific, but I'm feeling pretty overwhelmed by the task, to the point where I don't even feel confident I know what questions to even be looking for answers to. Any help that the community can give me on the topic would be greatly appreciated, even if it's just where and how to focus my thinking. Thanks for your help!

Hey so it would help if you could be a bit more specific.
But you can get the last row of a column like so:

Please Login or Register  to view this content.

Where the 1 represents the column number.
Then you can use that to reference the last cell of that range with something like Cells(lngRow,1) or Range(Cells(1,1),Cells(lngRow,1))