Hello all,
I'm pretty far into a rather gnarly excel workbook to track project status and billing data, and need some help to get it across the goal line. There is an overall Master sheet that keeps track of data for all current projects.
To add a new project, you can hit the "Generate Sheet" button at the top of the Master sheet and it creates a new project sheet, which is a copy of a hidden template.
The data in the Master Sheet then auto fills from each individual project sheet. The sheet index is dynamic depending on the names of open sheets. If you manually delete a sheet, the rows shift up to the top. This part works completely fine.
When a project is complete, I want to be able to export the line of data to an "Archive Sheet" that has the exact same layout as the "Master Sheet". All of the data in the Master is autofilled, each cell references the sheet that's titled with the project name in column B, which is a dynamic index of open sheets. I know this archive feature can be done with either a button for each line, or triggered by a value in each row (in this case column C which alternates between "Billing Complete" or "Billing Incomplete") I'd prefer to use a button so the data isn't exported the second the billing becomes complete.
Because everything in the Master is autofilled from each project sheet, when a sheet is deleted from the overall sheet index, the information is removed from the Master Sheet. Because of this, it seems another option would be a button in each project sheet that would send each value directly to the Archive (bypassing the Master sheet on the way), save it as a separate workbook, and delete it from the sheet index.
I've tried to put the "Archive Button" on the template sheet that is repeatedly copied, but am having issues with this because the sheet name is variable (name located in cell G4).
I've browsed through this forum and many others in search of code that will help me accomplish this, but nothing seems to work. Can anyone help walk me through this? Is it easier to just export the data from each individual project sheet and by-pass the Master Sheet, or is it just as efficient to have an archive button in the Master, and a Save/ Delete button in each individual project sheet?
I'm pretty far into a rather gnarly excel workbook to track project status and billing data, and need some help to get it across the goal line. There is an overall Master sheet that keeps track of data for all current projects.
To add a new project, you can hit the "Generate Sheet" button at the top of the Master sheet and it creates a new project sheet, which is a copy of a hidden template.
The data in the Master Sheet then auto fills from each individual project sheet. The sheet index is dynamic depending on the names of open sheets. If you manually delete a sheet, the rows shift up to the top. This part works completely fine.
When a project is complete, I want to be able to export the line of data to an "Archive Sheet" that has the exact same layout as the "Master Sheet". All of the data in the Master is autofilled, each cell references the sheet that's titled with the project name in column B, which is a dynamic index of open sheets. I know this archive feature can be done with either a button for each line, or triggered by a value in each row (in this case column C which alternates between "Billing Complete" or "Billing Incomplete") I'd prefer to use a button so the data isn't exported the second the billing becomes complete.
Because everything in the Master is autofilled from each project sheet, when a sheet is deleted from the overall sheet index, the information is removed from the Master Sheet. Because of this, it seems another option would be a button in each project sheet that would send each value directly to the Archive (bypassing the Master sheet on the way), save it as a separate workbook, and delete it from the sheet index.
I've tried to put the "Archive Button" on the template sheet that is repeatedly copied, but am having issues with this because the sheet name is variable (name located in cell G4).
I've browsed through this forum and many others in search of code that will help me accomplish this, but nothing seems to work. Can anyone help walk me through this? Is it easier to just export the data from each individual project sheet and by-pass the Master Sheet, or is it just as efficient to have an archive button in the Master, and a Save/ Delete button in each individual project sheet?
