+ Reply to Thread
Results 1 to 9 of 9

Assign a group of worksheets to a single variable

Hybrid View

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    9

    Assign a group of worksheets to a single variable

    I have a workbook with 50+ sheets. These sheets all need to be formatted, and they each fall into 1 of 4 "types", that need to be formatted differently. I want to write code that cycles through the whole workbook and applies the appropriate formatting based on the sheet type. I can't change the name of any of the sheets, and can't just hard code a value into a cell for the program to look for, because any of the cells are liable to change. How can I tell the program which set of formatting to use? I put an example of what I'm looking to do below, though obviously simplified, and I know it's not real code.

    Sub FormatSheets()
    Sheets("First") = Type1
    Sheets("Second") = Type2
    Sheets("Third") = Type1
    
    Dim ws as worksheet
    For each ws in activeworkbook.worksheets
    If ws.type = Type1
    ws.rows.entirerow.hidden = false
    ElseIf ws.type = Type2
    ws.rows(4).entirerow.hidden = true
    Next ws
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Assign a group of worksheets to a single variable

    How do you determine which formatting to apply?

    If there's no way to do that via code I would suggest you use arrays with hard-coded sheet names.

  3. #3
    Registered User
    Join Date
    08-06-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Assign a group of worksheets to a single variable

    I have a list of which sheet gets which formatting. How would using arrays work?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Assign a group of worksheets to a single variable

    What exactly do you have in the list?

  5. #5
    Registered User
    Join Date
    08-06-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Assign a group of worksheets to a single variable

    Two columns, first with sheet name, second with type number
    MSBI 3
    BSIN 2
    EnDD 1
    DPPA 2

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Assign a group of worksheets to a single variable

    Well we don't need an array, just a loop.

    Where is this list located?

    Let's say it's on Sheet1 and starts in A2.

    Dim wsForm As Worksheet
    Dim strName As Worksheet
    
    For I = 2 To Worksheets("Sheet1").Range("A" &Rows.Count).End(xlUp).Row
       
          strName = Worksheets("Sheet1").Range("A" & I).Value
    
          Set wsForm = Worksheets(strName) ' worksheet to be formatted
    
          Select Case Worksheets("Sheet1").Range("B" & I).Value
             Case 1
                 ' Apply type 1 formatting to worksheet wsForm
             Case 2
                 ' Apply type 2 formatting to worksheet wsForm 
             Case 
                 ' Apply type 3 formatting to worksheet wsForm
             Case 4
                 ' Apply type 4 formatting to worksheet wsForm
         End Select            
    
    Next I

  7. #7
    Registered User
    Join Date
    08-06-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Assign a group of worksheets to a single variable

    Thanks, that worked perfectly! Though for future users - needed to change Dim strName as String instead of worksheet, but I really appreciate the help.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Assign a group of worksheets to a single variable

    Oops, sorry about that.

    Wasn't going to use a variable but thought this might be a bit confusing.
     Set wsForm = Worksheets(Worksheets("Sheet1").Range("A" & I).Value)

  9. #9
    Registered User
    Join Date
    08-06-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Assign a group of worksheets to a single variable

    Oh, I like that too. Thanks, you've been a lot of help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1