Excel Macro to Update Connection String when Path and/or Filename Changed

Hi, all!

I have been working on an Excel workbook for awhile and I am finally conceding to the idea that I may not be able to pull this one off on my own. It's a bit different than any other code I've worked on previously.

I have two tables mirrored from another sheet within the same workbook. My boss wants all input updated on one sheet but the final results to show on multiple sheets to be printed for the client(s). However, whenever the file is saved with a new name or in a new location, the table stops updating. How do I get the path and filename to update whenever the file is saved?

The second issue that I've been unable to resolve is that if one or both tables contain no data, it needs to show only one blank row above the totals.

Here's what I have so far, which is tied to a "click to refresh" button within the Input sheet:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I did it for you this time. Please read forum rules. Thanks