• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto-naming worksheets in a workbook under certain conditions

O

OleMiss2010

Member
I am trying to write a macro that will auto name certain worksheets under certain conditions but I am missing a key element (see code below):


Sub Sheet_Naming()

Dim ws As Worksheet

Application.Volatile

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Summary" Then

If ws.Range("C5") <> "<>" Then

'I NEED A WAY RIGHT HERE TO SELECT THE FIRST SHEET THAT ISN'T NAME "SUMMARY" TO

'START THE PROGRAM, BUT THE SHEET NAME WILL LIKELY NOT BE CONSISTENT AND THIS

'MACRO NEEDS TO REPEAT FOR ALL SHEETS NOT NAMED "SUMMARY"

ActiveSheets.Name = ActiveSheets.Range("C5")

Sheets("Summary").Select

Next

End If

End If

End Sub
 
Is this what you're looking for?

[pre]
Code: 
Sub Sheet_Naming()
Dim ws As Worksheet
'Don't need this, only for functions
'Application.Volatile

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
If ws.Range("C5") <> "<>" Then
If ActiveSheet.Name = "Summary" Then
ws.Select
'Any other coding for first sheet?
End If
ws.Name = ws.Range("C5").Value
End If
End If
Next ws
End Sub
[/pre]
 
That almost does it. I get an error if "C5" is blank, which it might be in some cases and in those cases I would like the macro to skip that worksheet and let the name go unchanged.
 
To skip C5 if it's empty, change this line:


If ws.Range("C5") <> "<>" Then


to:


If ws.Range("C5") <> "<>" and ws.Range("C5") <> "" Then
 
For first question, change this line

[pre]
Code: 
If ws.Range("C5") <> "<>" Then
to this:

If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then
[/pre]
For the 2nd question, I wasn't sure if there was anything else that needed to happen to first sheet. You mentioned "starting the program", and I wasn't sure what that was.
 
Luke, what's the reason for executing the Select method on ws? Or checking ActiveSheet.Name in this loop -- I wouldn't expect the active sheet to change unless you .Activate another sheet.
 
This was very helpful Luke M and Vaskov17. I'm still learning VBA and Macro writing and I'm basically doing it through reading other people's codes and learning the different possibilities. I really appreciate all of the help.
 
Asa,

In OleMiss's first post, he mentioned that he wanted to select the first sheet that wasn't the summary sheet. That's the only reason for that bit of code. You are correct, you wouldn't normally use Select within a loop. The intent of the code is to only perform the select once.
 
OK, I see.

The behavior is rather odd, though.


Assuming a workbook with the first sheet named Summary and the others potentially being renamed:

If "Summary" is the ActiveSheet when you run the macro, the next sheet that has a name in C5 will be selected and renamed.

Then the remaining sheets will be renamed but not selected.


If any sheet other than "Summary" was active when running the macro, the .Select method will never execute, although all the sheets will be renamed appropriately.


Essentially it selects the first sheet it was able to rename, but only if the macro is run when "Summary" is active.


Also there is a potential issue with assuming that ThisWorkbook and ActiveSheet refer to the same workbook. Probably not critical though.

If that first renamed sheet is the one intended to be selected, to select it under all conditions, I propose this version:

[pre]
Code: 
Sub Sheet_Naming()
Dim ws As Worksheet, SelectionChanged As Boolean

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then
If Not SelectionChanged Then
ws.Select
SelectionChanged = True
End If
ws.Name = ws.Range("C5").Value
End If
End If
Next ws
End Sub
[/pre]
 
If the first sheet other than "Summary" even if it wasn't renamed, then move

[pre]
Code: 
If Not SelectionChanged Then
ws.Select
SelectionChanged = True
End If
before

If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then
[/pre]
 
asa,

You've got it exactly.

True, it might be dangerous using the ActiveSheet reference...on the other hand, that bit of code is already nested within a previous If statement of:

[pre]
Code: 
If ws.Name <> "Summary" Then
[/pre]
so we're guaranteed never to end up on the Summary sheet. I like your version for it's clarity, though.
 
So here's a new wrinkle I've encountered. In cases where C5 is the same as another sheet name I get an error message. I'm trying to insert an Error Handler, but it isn't working properly. Let's say I have two sheets where cell C5 says Bob. I want one of the C5s to change to Bob(1) so that the sheet will be named Bob(1) then I will have a Bob and a Bob(1) sheet. If there is a third Bob in C5 I'd like that to become Bob(2) and soforth. Here's the code I wrote to handle this that isn't quite behaving correctly:

[pre]
Code: 
Sub Sheet_Naming()
Dim ws As Worksheet, SelectionChanged As Boolean

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then
If Not SelectionChanged Then
ws.Select
SelectionChanged = True
End If
ws.Name = ws.Range("C5").Value
On Error GoTo ErrHandler
End If
End If
Next ws

ErrHandler:
Range("C5:I5").Select
Selection.Copy
Range("L5").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-9]&""(1)"""
Range("L5:R5").Select
Selection.Copy
Range("C5:I5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L3:R3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L5:R5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Resume Next

End Sub
[/pre]
 
Need to put the error handler statement before the line you expect an error to occur. Rather than copying to some sheet (and be careful about which sheet the code is actually referring to) you could keep it all within the VB by doing something like this:

[pre]
Code: 
Sub Sheet_Naming()
Dim ws As Worksheet, SelectionChanged As Boolean
Dim CellValue As String
Dim i As Integer
Dim NewName As String

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then
If Not SelectionChanged Then
ws.Select
SelectionChanged = True
End If
i = 0
CellValue = ws.Range("C5").Value
NewName = CellValue
On Error GoTo ErrHandler
ws.Name = NewName
'Only want to goto error handler if problem
'is here, so we reset the On Error statement
On Error GoTo 0
End If
End If
Next ws

Exit Sub

ErrHandler:
i = i + 1
NewName = CellValue & "(" & i & ")"
'We want to go back to the line that had a problem,
'not the next line, so we just use a regular Resume
Resume

End Sub
[/pre]
 
I need cell C5 to also rename as Bob(1). So I think I need my Error Handler to rename C5 then allow the sheet to be renamed to match C5. As it stands now the Sheet Name is renaming as Bob(1) but C5 is remaining Bob. I took a stab at fixing it but this isn't quite right.

[pre]
Code: 
ErrHandler:
i = i + 1
ws.Range("C5").Select
Selection.Copy
ws.Range("L5").Paste
Application.CutCopyMode = False
ws.Range("L5") = CellValue & "(" & i & ")"
ws.Range("L5").Copy
ws.Range("L5").PasteSpecial Paste:=PasteValues, operation:=None, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ws.Range("L5").Select
ws.Range("L5").Copy
ws.Range("C5").Paste
Application.CutCopyMode = False
ws.Range("L3:R3").Select
Selection.Copy
ws.Range("L5:R5").Select
Selection.Paste
Application.CutCopyMode = False
NewName = CellValue
'We want to go back to the line that had a problem,
'not the next line, so we just use a regular Resume
Resume
[/pre]

I also think that the On Error GoTo ErrHanlder may have to move up a few lines, but I'm not sure on that. Any thoughts?
 
Just add this one line:

ws.Range("C5") = ws.Name


after the On Error Goto 0 command.
 
The 2nd line of this?

[pre]
Code: 
ErrHandler:
i = i + 1
NewName = CellValue & "(" & i & ")"
'We want to go back to the line that had a problem,
'not the next line, so we just use a regular Resume
Resume
[/pre]
Make sure the double quotes got copied correctly. Sometimes that get changed when you copy from a web page into slanted double quotes, which VB doesn't recognize.
 
This has been an interesting and extremely educational sub to write. I really appreciate all of your help on this Luke M and your help as well Asa.
 
Glad to offer my little contribution, OleMiss!


One more thing.. Depending on how concerned you are about making the routine bullet-proof, you could have the error handler make sure that the specific error raised is the one you expect. If the sheet couldn't be renamed due to worksheet protection, or just some completely unanticipated error, your routine will keep trying to solve the problem by incrementing numbers at the end of the name.. hanging your process and probably eventually causing some other error (tries a number greater than the highest allowed integer or something). You might also want to have it quit trying/raise a fatal error if the incremented number hits some maximum value of i (100? 200?) without succeeding.


The help entries for the On Error and Resume statements, the Err object, the Err.Number property, and the err.Raise method all have examples, and you can determine the specific error you need to test for by setting a breakpoint in your error handler (hit F9 on a line) causing execution to stop there. then you can type ? err.number in the immediate window to find the error number. You could also add err.number as a Watch (Debug menu) or even the whole Err object as a Watch if you were debugging/testing code that could generate any number of errors and save the trouble of retyping ? err.number and/or ? err.description each time.


If you have a large name of worksheets that will be renamed in this loop, especially if many of them might have the same name in C5, your system/Excel could become unresponsive during the long code execution. With Any loops that go more than a second or so it's a good idea to include DoEvents periodically to allow events to fire. This will allow the user and the windows kernal to interact with the Excel window/process, and share the CPU better with other processes. Since the main loop is a loop(!), and the error routine is looping back to ws.Name=... until it succeeds, you could include the line just once to handle both by including a line label followed by the DoEvents line right before the ws.name=... line and then changing the Resume line at the end of your error handler to Resume
Code: 
label
... If your routine isn't going to run for very long there's no need. If you do include it, ideally you wouldn't have it execute every time through the loop as that's not very CPU-efficient. Instead, you could have it execute once every X milliseconds or every X iterations through the loop (referencing the Timer function in the former and incrementing a counter for the latter).


The DoEvents help entry has an example of executing DoEvents every X times through a loop.


Asa
 
Asa,


Your hypothesis appears to have come true about the large number of worksheets causing a problem with a long loop. I have suddenly (after using this for several weeks now) started encountering a problem with the macro becoming unresponsive and giving me an overflow error.


Can you possibly explain how the DoEvents work in more detail and how I might incorporate them into my code to solve this problem?
 
You must log in or register to reply here.
Back
Top