Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: VBA Select Multiple Sheets of Varied/Changing Sheet Names

  1. #1
    Join Date
    18th May 2006

    VBA Select Multiple Sheets of Varied/Changing Sheet Names

    Greetings ...

    How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? Please advise.

    New to forum, so apologies if format is not kosher.

    I have a group of 50+ workbooks which are all set up in the following format:

    -Contains 30+ sheets.
    -Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible.
    -However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.

    Here is my problem:

    I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets.

    The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.

    Many thanks for all help offered.

  2. #2
    Join Date
    11th March 2005
    Los Angeles

    Re: VBA Select Multiple Sheets of Varied/Changing Sheet Names

    for each ws in sheets
    if instr(ws.name, "Q") > 0 then 
    end if
    Eternity is a terrible thought. I mean, where's it going to end??

  3. #3
    Join Date
    18th May 2006

    Re: VBA Select Multiple Sheets of Varied/Changing Sheet Names

    Greetings ...

    Thank you for the reply.

    I tried the code, but could only get it to select the second sheet with a "Q" in it. I don't think I was filling in the blanks after the THEN statement correctly.

    But I managed to get it to work by combining it with code from another thread. Here it is:

    Sub Test()
        Dim blnReplace As Boolean
        Dim sht As Object
        blnReplace = True
        For Each ws In sheets
        If InStr(ws.Name, "Q") = 0 Then
                ws.Select blnReplace
                blnReplace = False
            End If
    End Sub
    I don't really understand how the blnReplace is making it so multiple sheets are selected together instead of one, but I will take it.

    Many, many thanks for the help.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 14
    Last Post: September 15th, 2008, 18:27
  2. Sheet Indirection. Refer To Sheets With Changing Names
    By econstantin in forum EXCEL HELP
    Replies: 6
    Last Post: February 26th, 2008, 17:58
  3. Hyperlink Index To Changing Sheets Tab Names
    By nkeckhar in forum EXCEL HELP
    Replies: 5
    Last Post: January 12th, 2008, 01:16
  4. Hyperlink To Changing Sheet Names
    By drltr6 in forum EXCEL HELP
    Replies: 2
    Last Post: October 9th, 2007, 13:25
  5. Replies: 2
    Last Post: March 29th, 2004, 08:17


Posting Permissions

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