Hi Guys,
I have a spreadsheet that consolidates a number of projects (each with their own tab) into a summary tab. On it there is a section for the budget, the current forecast and the previous forecast and I am trying to add a macro that I can run at the start of each month that will copy the current forecast into the previous forecast as values only.
I've recorded a macro that selects all the sheets between the Start and End point which does this, unfortunately the spreadsheet is live and new projects get started all the time. So what I really want is a way to change from Excel creating an array like below but rather to select all the sheets between Start and End regardless of how many are added/removed in each month
Sheets(Array("Start", "AH (Template)", "AH (1)", _
"AH (2)", "AH (3)", "AH (4)", _
"AH (5)", "AH (6)", "AH (7)", _
"AH (8)", "AH (9)", "AH (10)", _
"AH (9)", "AH (10)", "AH (11)", _
"AH (12)", "AH (13)", _
"AH (14)", "AH (15)", "AH (16)", _
"End")).Select
Everything else in the macro works as I expect I just would really rather find a way of not having to edit the macro each month and add/remove projects from the selection array if at all possible.
Knowledge wise I can happily record macro's and basic levels of editing them but not much more.
Thanks in advance for any help,
Tony
I have a spreadsheet that consolidates a number of projects (each with their own tab) into a summary tab. On it there is a section for the budget, the current forecast and the previous forecast and I am trying to add a macro that I can run at the start of each month that will copy the current forecast into the previous forecast as values only.
I've recorded a macro that selects all the sheets between the Start and End point which does this, unfortunately the spreadsheet is live and new projects get started all the time. So what I really want is a way to change from Excel creating an array like below but rather to select all the sheets between Start and End regardless of how many are added/removed in each month
Sheets(Array("Start", "AH (Template)", "AH (1)", _
"AH (2)", "AH (3)", "AH (4)", _
"AH (5)", "AH (6)", "AH (7)", _
"AH (8)", "AH (9)", "AH (10)", _
"AH (9)", "AH (10)", "AH (11)", _
"AH (12)", "AH (13)", _
"AH (14)", "AH (15)", "AH (16)", _
"End")).Select
Everything else in the macro works as I expect I just would really rather find a way of not having to edit the macro each month and add/remove projects from the selection array if at all possible.
Knowledge wise I can happily record macro's and basic levels of editing them but not much more.
Thanks in advance for any help,
Tony