Newbie Seeking Help with Ranges, Loops, and Select

[USNA91]

Greetings,

It has been a LONG time since I programmed in BASIC. I'm talking "Commodore 64 circa 1986" long time (!!!).

I am now learning VBA because I want to have Excel do things that formulas just won't do, and have some questions that I just can't seem to figure out.

WORKBOOK STRUCTURE:

My workbook has 32+ sheets:

• "Setup" contains up to 30 people's names, entered as first and last names in different cells.
• "Lookups and Calculations" performs calculations on the names given in "Setup", and determines what the Worksheet Name(s) will be. It concatenates full names as well as calculates the first initial and last name of each person. If the cells in "Setup" are blank, the calculated Worksheet Name for each worksheet defaults to "TM 01", "TM 02", and so forth up to "TM 30". ("TM" stands for "Team Member").
• The other 30 sheets are identical, and serve as the data-entry sheet for each Team Member.
• There are several other sheets that take data from these 30 sheets and do all sorts of things, but none of those are relevant to the problem at hand.

USER REQUIREMENTS:

When a list of names has been entered in the "Setup" worksheet, I need a routine that will do the following on command:

• If the calculated Worksheet Name is "TM 01" or any other number, it deletes any data entries in the sheet, sets the "Latest Update" field to "Pending", selects the home cell, and hides the worksheet.
• If the calculated Worksheet Name is that of a Team Member, it sets the Tab Name of that worksheet to the Team Member's Name and shows the worksheet.

CURRENT CODE:

The code I have written to do this its as follows:

Sub UpdateWorksheets()
Application.ScreenUpdating = False

Sheet11.Name = Worksheets("Lookups and Calculations").Range("K4").Value
If Sheet11.Name = Worksheets("Lookups and Calculations").Range("J4").Value Then
Sheet11.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
Sheet11.Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
Sheet11.Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
Sheet11.Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
Sheet11.Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
Sheet11.Range("C4") = "Pending"
Sheet11.Range("C7").Select
Sheet11.Visible = False
Else
Sheet11.Visible = True
End If

Sheet12.Name = Worksheets("Lookups and Calculations").Range("K5").Value
If Sheet12.Name = Worksheets("Lookups and Calculations").Range("J5").Value Then
Sheet12.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
Sheet12.Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
Sheet12.Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
Sheet12.Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
Sheet12.Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
Sheet12.Range("C4") = "Pending"
Sheet12.Range("C7").Select
Sheet12.Visible = False
Else
Sheet12.Visible = True
End If

Sheet13.Name = Worksheets("Lookups and Calculations").Range("K6").Value
If Sheet13.Name = Worksheets("Lookups and Calculations").Range("J6").Value Then
Sheet13.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
Sheet13.Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
Sheet13.Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
Sheet13.Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
Sheet13.Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
Sheet13.Range("C7").Select
Sheet13.Range("C4") = "Pending"
Sheet13.Visible = False
Else
Sheet13.Visible = True
End If

Application.ScreenUpdating = True
End Sub

Note: The code has been truncated to show only the first 3 of 30 worksheets. The other 27 are identical. Also, I am using the worksheet Code Name vs. Sheet Name because the Sheet Name is being changed.


WHERE HELP IS REQUESTED:

I am seeking help with the following issues:

1. With the exceptions below, the code above works, but obviously it is not very efficient. I have had significant trouble trying to code a simple loop that would go from 1-30 (If using Code Name numbers, I reckon it would be from 11 to 40) and just perform the same steps. If I use "Count" as the variable, I don't know how to get the equivalent of "If Sheet(Count)=...". The Range reference with all of them together throws an error. Yes, I compounded them correctly (commas, colons, etc.), but it refuses to work. Is there a length limit to compound ranges?
2. The command SheetXX.Range("C7").Select (where XX is the sheet number) throws an error. If I precede it with SheetXX.Select, then THAT statement throws an error. I can't figure out why.
3. I tried using a "With Sheet XX" grouping, but it threw the same errors regarding the Select functionality.

If I remove the problematic Select functions, the code above correctly names the worksheets, deletes exiting contents as required/desired, and hides/unhides sheets correctly.

So, there it is.... A big post for a first post, but I've prowled around on here long enough to know I can find the help I need.

Thanks in advance!

 

[USNA91]

Apologies, but I made a formatting mistake above and can't figure out how to edit the post...

What to meant to say was:

WHERE HELP IS REQUESTED:

I am seeking help with the following issues:

1. With the exceptions below, the code above works, but obviously it is not very efficient. I have had significant trouble trying to code a simple loop that would go from 1-30 (If using Code Name numbers, I reckon it would be from 11 to 40) and just perform the same steps. If I use "Count" as the variable, I don't know how to get the equivalent of "If Sheet(Count)=...".
2. The ClearContents function is broken into 5 steps because the Range reference with all of them together throws an error. Yes, I compounded them correctly (commas, colons, etc.), but it refuses to work. Is there a length limit to compound ranges?
3. The command SheetXX.Range("C7").Select (where XX is the sheet number) throws an error. If I precede it with SheetXX.Select, then THAT statement throws an error. I can't figure out why.
4. I tried using a "With Sheet XX" grouping, but it threw the same errors regarding the Select functionality.

Thanks again!

 

[bobsan42]

Welcome to the forum.

to use the select method the object to be selected must be on the active sheet - you cannot select something you don't see

With SheetXX
    .Activate
    .Range("C7").Select
end with

But it's hardly worth it selecting something just before you hide it.

 

[USNA91]

Dear Bob,

Thanks! Obviously I was confusing ACTIVATE and SELECT. I'll look into that right now.

Regarding being worth it... Normally I would agree, but in this case the reason I'm doing it is so that if the sheet is later assigned to someone else, when they open the sheet, the home data cell is already selected. Just trying to make it easier for them.

Thanks again, and off to the VBA book...

 

[bobsan42]

About the rest - it all feels very strange. Admittedly I don't understand your setup and all the ideas and logic behind it, so forgive if I say something stupid.
- Referring directly to the sheet objects is quite rare
- each sheet is connected with a particular row/cells in Lookups and Calculations or Setup - this is also a bit odd and asking for trouble
- I myself would probably delete a sheet I don't need, and then create a new one when needed (maybe have a hidden template sheet to copy) - too much trouble to rename, cleanup and hide and then not use it.
- A lot of people use this approach, and then it's too late to change it. I personally advise everyone I can and dare not to scatter their data among many sheets and then try to gather it together and make sense of it. My preferred approach it to put everything similar together in a table and then use a single sheet or form to present a selected set of data. Then it is easier to analyze the data in any way you like.

 

[bobsan42]

and a bit shorter code:

'@Folder("VBAProject")
Option Explicit


Sub UpdateWorksheets()
    Dim wsh As Worksheet
    Set wsh = Worksheets("Lookups And Calculations")
   
    updateAworksheet Sheet11, wsh.Range("K4").Value, wsh.Range("J4").Value
    updateAworksheet Sheet12, wsh.Range("K5").Value, wsh.Range("J5").Value
    updateAworksheet Sheet13, wsh.Range("K6").Value, wsh.Range("J6").Value
    '... etc.
   
    Set wsh = Nothing
    End Sub

Private Sub updateAworksheet(ByRef sh As Worksheet, _
                             ByVal shName As String, _
                             ByVal checkName As String)
    With sh
        .Name = shName
        If shName = checkName Then
            .Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
            .Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
            .Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
            .Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
            .Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
            .Range("C4") = "Pending"
            .Activate
            .Range("C7").Select
            .Visible = False
        Else
            .Visible = True
        End If
    End With
      
    End Sub

 

[USNA91]

No argument with any of that. I'm fairly good with Excel, but I have no delusions that I'm an expert at data structures.

The use of this worksheet is to capture the availability and location of team members on a project. The Setup sheet allows the Project Manager to enter the calendar year, the team member names, the facility names, lodging options, etc.

The sheets used by each Team Member is basically a calendar for the year with two drop-downs for status and location for each day, and a few cells for car rental, lodging, notes, etc. I chose the number 30 because even our largest project rarely hits 20, but I like being prepared. As the Setup sheet is prepared, each TM sheet then pulls the name, client name, project name, dates, etc. from the setup sheet.

Other sheets then create team calendars (weekly and annually) where all the team members and facility availabilities are presented together, who is at which facility, yada, yada, yada...

I agree that having 30 sheets in existence all the time when, say, there are only 12 active team members is probably not efficient, but I haven't yet learned enough VBA to have a single template that is then copied X number of times whenever a project is set up. So I'll just keep a standard set and hide/unhide to keep it simple for the user. It's a brute-force approach, but I'm not knowledgeable enough for the finesse approach yet.


On a related note, it would seem that the argument for the Range function is limited to 255 characters, hence the issue I was having. That means I need to learn how to name and combine ranges. Sigh...

 

[USNA91]

WHOA! Gonna print that and study it.

Thanks!

 

[USNA91]

Ok... So if I'm reading this correctly...

Sub UpdateWorksheets calls private sub updateAworksheet and passes three variables to be worked on, which enter the private sub as variables sh, shName, and CheckName.

The private sub does the comparing, updating, etc. Upon completion, it passes back to UpdateWorksheets, which then calls the private sub again using the next set of three variables. Wash, rinse, repeat.

So the private sub is basically a GOSUB command from the old days. Got it.

Gotta learn about declaring variables next. Bot used to declaring a variable as an object. Heck, just getting used to objects at all!

Thanks for that help!

So just for chuckles, how could I use a loop to further simplify the UpdateWorksheets sub to avoid having the same row 30 times?

 

[bobsan42]

Just another version of the private sub to get rid of the long range addresses.

Option Explicit

Sub UpdateWorksheets()
    Dim wsh As Worksheet
    Set wsh = Worksheets("Lookups And Calculations")
    application.ScreenUpdating = False
  
    updateAworksheet Sheet11, wsh.Range("K4").Value, wsh.Range("J4").Value
    updateAworksheet Sheet12, wsh.Range("K5").Value, wsh.Range("J5").Value
    updateAworksheet Sheet13, wsh.Range("K6").Value, wsh.Range("J6").Value
    '... etc.
  
    application.ScreenUpdating = True
    Set wsh = Nothing
    End Sub

Private Sub updateAworksheet(ByRef sh As Worksheet, _
                             ByVal shName As String, _
                             ByVal checkName As String)
    Dim rng As Range, cc As Range
    With sh
        .Name = shName
        If shName = checkName Then
            Set rng = .Range("J6:L161")
            Set cc = .Range("C7:I8")
            Do
                Set rng = Union(rng, cc)
                Set cc = cc.Offset(3, 0)
            Loop Until cc.Row > 160
            rng.ClearContents
            .Range("C4") = "Pending"
            .Activate
            .Range("C7").Select
            .Visible = False
        Else
            .Visible = True
        End If
    End With
    End Sub