Results 1 to 8 of 8

Thread: Macro to select all tabs between start and end

  1. #1

    Macro to select all tabs between start and end



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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 ?

  3. #3
    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?

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  5. #5
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    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

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    @ 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 ?

  7. #7
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    Indexnumbers aren't static. So the result will be unpredictable.

  8. #8
    hey thanks to both of you for your help, this has worked and does what I need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •