I apologize, I'm struggling and those wiser than I on this forum may have a quicker solution.
I have a very nifty costing estimate spreadsheet I wrote for doing project cost estimates. I set it up to do some cool things BUT if someone wants to add a task (set of rows) and they don't know what they're doing it's very easy to mess up.
This is when I decided a macro to insert a blank standard "task" via button would come in handy. I'm having trouble because it's very easy to do once by recording a macro but the relative references get messed up when you try to run it again.
I have a dummy blank task/set of rows hidden at the bottom of my sheet. Relative references don't work because if you copy it and append to bottom of list of tasks the relative location isn't the same then next time you run the macro. SO what I think might work is I have text values hidden in the A column that are used for the SUMIFs in the subtotals section at the bottom. I'd like to create a macro that does the following:
1) for all cells in the A column = "Task X*" copy the entire row
2) insert entire selection of rows that meet criteria above 2 rows above the row in which the A cell = "Sum1"
I've tried combos of if thens and match and I'm missing someting...
This will appropriately append the blank task collection of rows (that have the nifty appropriate formula) to the bottom of the task section because the "Sum1" row is the beginning of the subtotals section. I figure if you get me that far I can alter it to also add a subtotal row for that task.
Thanks in advance
I have a very nifty costing estimate spreadsheet I wrote for doing project cost estimates. I set it up to do some cool things BUT if someone wants to add a task (set of rows) and they don't know what they're doing it's very easy to mess up.
This is when I decided a macro to insert a blank standard "task" via button would come in handy. I'm having trouble because it's very easy to do once by recording a macro but the relative references get messed up when you try to run it again.
I have a dummy blank task/set of rows hidden at the bottom of my sheet. Relative references don't work because if you copy it and append to bottom of list of tasks the relative location isn't the same then next time you run the macro. SO what I think might work is I have text values hidden in the A column that are used for the SUMIFs in the subtotals section at the bottom. I'd like to create a macro that does the following:
1) for all cells in the A column = "Task X*" copy the entire row
2) insert entire selection of rows that meet criteria above 2 rows above the row in which the A cell = "Sum1"
I've tried combos of if thens and match and I'm missing someting...
This will appropriately append the blank task collection of rows (that have the nifty appropriate formula) to the bottom of the task section because the "Sum1" row is the beginning of the subtotals section. I figure if you get me that far I can alter it to also add a subtotal row for that task.
Thanks in advance
