T
Todd Huttenstine
I have a list of names in column B starting in cell 3 going down on sheet
"Customer Records"(sheet1). This list will continue to grow. I would like
a code that will count the number of names from cell B3 down and then unhide
that many number of sheets starting with sheet 3 as well as rename each
sheet by the name listed in each corresponding cell. For instance, there
are 5 people in the list. The name Jeff is in B3, Tom in B4, Nancy in B5,
Jim in B6, and Pat in B7. The code must unhide 5 sheets(which would be
sheets3-7) and rename each sheet according to the name of the corresponding
cell starting with sheet3. For instance, sheet3 unhides and is renamed
Jeff, sheet4 unhides and is renamed Tom, sheet5 unhides and is renamed
Nancy, etc... Now lets say there is no sheet5 for Nancy, I will need the
code to create sheets as needed. For instance. If there are only 4 sheets
in the workbook, then I need for a 5th sheet to be created and then renamed
Nancy. Also If a name is taken out of the list, I need for the code to hide
the corresponding sheet.
So far I have the following code below:
Private Sub CommandButton3_Click()
Dim cRows As Long
Dim i As Long
With Worksheets("Customer Records")
cRows = .Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To cRows
Worksheets(i).Visible = True
Worksheets(i).Name = .Cells(i, "B").Value
Next i
End With
End Sub
What do I add in this code for it to hide the corresponding sheet when a
name in the list is deleted? Like lets say I delete the 3rd record, then I
would need sheet number 3 after the fiorst 2 sheets to hide. This means
sheet number 5 should hide. Also what is the code to get it to create a
sheet when needed? Right now the code will only work if there are enough
sheets for the amount of records in the list. I want a sheet to auto add to
the workbook if there are not enough sheets.
thanx
Todd Huttenstine
"Customer Records"(sheet1). This list will continue to grow. I would like
a code that will count the number of names from cell B3 down and then unhide
that many number of sheets starting with sheet 3 as well as rename each
sheet by the name listed in each corresponding cell. For instance, there
are 5 people in the list. The name Jeff is in B3, Tom in B4, Nancy in B5,
Jim in B6, and Pat in B7. The code must unhide 5 sheets(which would be
sheets3-7) and rename each sheet according to the name of the corresponding
cell starting with sheet3. For instance, sheet3 unhides and is renamed
Jeff, sheet4 unhides and is renamed Tom, sheet5 unhides and is renamed
Nancy, etc... Now lets say there is no sheet5 for Nancy, I will need the
code to create sheets as needed. For instance. If there are only 4 sheets
in the workbook, then I need for a 5th sheet to be created and then renamed
Nancy. Also If a name is taken out of the list, I need for the code to hide
the corresponding sheet.
So far I have the following code below:
Private Sub CommandButton3_Click()
Dim cRows As Long
Dim i As Long
With Worksheets("Customer Records")
cRows = .Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To cRows
Worksheets(i).Visible = True
Worksheets(i).Name = .Cells(i, "B").Value
Next i
End With
End Sub
What do I add in this code for it to hide the corresponding sheet when a
name in the list is deleted? Like lets say I delete the 3rd record, then I
would need sheet number 3 after the fiorst 2 sheets to hide. This means
sheet number 5 should hide. Also what is the code to get it to create a
sheet when needed? Right now the code will only work if there are enough
sheets for the amount of records in the list. I want a sheet to auto add to
the workbook if there are not enough sheets.
thanx
Todd Huttenstine