Macro to select all tabs between start and end

tony7262

New member
Joined
Feb 20, 2015
Messages
3
Reaction score
0
Points
0
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
 
Can you elaborate on "select all the sheets between Start and End" ?

Does this mean you move the position of the "Start" and/or "End" sheets in the tab lineup as a method of determining what sheets you want to select ?

Or are you saying you want to select "Start" and "End" and all the other sheets who's name starts with "AH" no matter how many of these there are or where they are in the tab lineup ?
 
Hi

I have added a sheet called 'Start' and a sheet called 'End' and I add all the additional sheets between that are called 'AH etc' .

I would like to select all the sheets between the one called Start and one called End (doesn't matter if Start and End are selected as well as they are blank) to be selected by the macro so I can use the next part of the macro that already have written to do the copy and paste parts.

Hopefully makes a bit more sense?
 
Try something along the lines of this.
"Start" must be ahead of "End" and there must be at least 1 sheet between them.
Code:
    Dim i As Integer
    Dim sh As Worksheet
    Dim WantedSheets As String

For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    Set sh = Sheets(i)
    WantedSheets = WantedSheets & "|" & sh.Name
Next i

Sheets(Split(Mid(WantedSheets, 2), "|")).Select
 
You'd better create an array excluding the sheets you don't need:

If you want to exclude the sheets 'first', 'last' and 'contents':

Code:
sub M_snb()
  for each sh in sheets
    if instr("firstlastcontents",sh.name)=0 then c00=c00 & "|" & sh.name
  next
  sheets(split(mid(c00,2),"|")).select
End sub
 
@ snb

Please, educate me here, why would exclusions be necessary ?

The way I read this thread the OP is using the sheets named "Start" and "End" as "bookends" to identify the sheets they want selected.
Based on the index numbers of the sheets, wouldn't that automatically exclude all other sheets ?
 
Indexnumbers aren't static. So the result will be unpredictable.
 
hey thanks to both of you for your help, this has worked and does what I need.
 
Back
Top