Edit consolidation macro to make it usable in any workbook

I am creating a template workbook which consists of a project forecast sheet for any number of projects and with a summery sheet at the begging. Each sheet is identical so, I created a macro to sum a range of data from each project sheet (R7C7:R18C16) and to place it in the same location on the summery sheet (Range("G7").Select). The macro works wonderfully and I have since attached it to a button. The idea is, the end user would be able to manipulate the data within the range R7C7:R18C16 of any project sheet and then re-run the macro by clicking the button within the summery sheet. Unfortunately, the macro recorded the exact file path of my workbook.

Question 1.) Is there a way to write this Macro so it will work in any active workbook? Meaning, once a project manager relabels/saves the workbook in a different location with a different name, the macro will still perform.

Question 2.) Can the macro be written such that the worksheet name can be altered and not affect the algorithm? As of right now, if the tab name of any project sheet is changed, the macro will no work.

Question 3.) Is there a way to write the macro to an undetermined amount of project sheets? In the example below I only have 6, but if an end user were to add 6 more projects, I would like the macro to collect all 12 in the summery.
I have trouble shot this all day with not much to show for it and would be greatly thankful for any help or thoughts. Thanks!

RH


Here is my code to date:

Please Login or Register  to view this content.

AlphaFrog - this was the ticket. And I didn't have to change Sheet1 at all either. I greatly appreciate the help. Karma points heading your way