troubleshooting Question

Macro for Merging new Files/Worksheets from different Workbooks

Avatar of Bright01
Bright01Flag for United States of America asked on
Microsoft Excel
9 Comments2 Solutions359 ViewsLast Modified:
EE Professionals,

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,

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros