I have a very large data "Worksheet" (let's call it Worksheet A), that gets updated each week with new information. The name on the Tab identifies the particular "Week" that the sheet is good for and I need to keep a record of each new sheet that comes in. Finally, in order to produce a "dashboard" I have created a Worksheet that has been laid out as a table and has specific range names and macros that are used to create a dashboard on another tab.
Ideally, it would be great if there was a Macro/Utility with a form that would pop up and ask source Workbook, Worksheet, Start Cell and then auto import the sheet into a destination Workbook and replace an existing table, then rename the worksheet (again, input into the form). This probably doesn't exist.
So instead, here's what I need a macro to do. Each week that a new sheet comes in, I need to open my workbook and "move" Worksheet A into my Workbook. I'd then have a button on my existing Spreadsheet that would activate the macro and the macro would need to make a copy of my existing Worksheet then recognize the new sheet and a starting point (cell) that could be pre-programmed (it's the same each week) and then recognize where the data row stopped (it's different each week); then do a copy and paste into the freshly duped new worksheet. I would then have to manually change the name.
Does that make sense? Will you need a sample or have you heard of this being done before? I'm looking for simplicity and yet the ability to get this done automatically.
Thank you in advance,