Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    Posts
    2

    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.
    lulasi

  2. #2
    Join Date
    11th March 2005
    Location
    Los Angeles
    Posts
    500

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

    VB:
    For Each ws In sheets 
        If instr(ws.name, "Q") > 0 Then 
             
        Else 
             
        End If 
    
    
    Eternity is a terrible thought. I mean, where's it going to end??

  3. #3
    Join Date
    18th May 2006
    Posts
    2

    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:

    VB:
    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 
        Next 
    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.
    lulasi

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, 17:27
  2. Sheet Indirection. Refer To Sheets With Changing Names
    By econstantin in forum EXCEL HELP
    Replies: 6
    Last Post: February 26th, 2008, 16:58
  3. Hyperlink Index To Changing Sheets Tab Names
    By nkeckhar in forum EXCEL HELP
    Replies: 5
    Last Post: January 12th, 2008, 00:16
  4. Hyperlink To Changing Sheet Names
    By drltr6 in forum EXCEL HELP
    Replies: 2
    Last Post: October 9th, 2007, 12:25
  5. Replies: 2
    Last Post: March 29th, 2004, 07:17

Bookmarks

Posting Permissions

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