Programatically Naming Worksheets (using catenated values)

[Guest]

Hi,

I have my code all set to name worksheets from names in Column D (D15 to
D144). Now, rather than using only the values (employee names) in Column D
(user inputted ad hoc), I need to name the worksheets using the concatenated
value of the name in Column D and a number in Column E. For example, a user
enters "Smith, Fred" in D15 and "123456" in E15. I need a macro to name the
corresponding worksheet "Smith, Fred (123456)" and not just "Smith, Fred".
I've had some amazing help already by Tom Ogilvy, Bob Phillips, Jim
Thomlinson and Dave Peterson resulting in the following code:

Private Sub CommandButton3_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "d").End(xlUp)
Set Rng = ws.Range("d15", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(cell.Value)
On Error GoTo 0
If ws Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value

cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
End If
End If
Next
Application.Goto Reference:="Summary"

End Sub

Thanks in advance!

 

[Guest]

Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value

 

[Guest]

Thanks Gary's Student! I was wondering how I could name the worksheet name
with the concatenated value surrounded by parenthesis. For example, "Lysell,
Kent(123456)" rather than "Lysell, Kent123456".

Thanks!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Susan]

cell.Value & "(" & cell.Offset(0,1).Value & ")"

this should work
susan

 

[Guest]

Thanks Susan!

It did work until the about 20 or so names had been assigned to tabs. The
noted error in Excel referred to as "Copying worksheet programmatically
causes run-time error 1004 in Excel" precluded me from continuing. Rather
than pointing to the error above represented by the code:

Sheets("Master").Copy after:=Worksheets(Worksheets.Count)

after 20 or so names, Excel pointed to the line below that you and Garry"s
Student had helped me on:
ActiveSheet.Name = cell.Value & "(" & cell.Offset(0, 1).Value & ")"

The normal route of saving, closing, and re-opening the spreadsheet to rerun
the macro to complete the list of names being assigned to worksheets did not
resolve the problem.

Any ideas?

TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Susan]

a) if you step thru the code, where it gets hung up hover your mouse
over ActiveSheet.Name, and cell.Value, and cell.Offset(0, 1).Value.
if it's hanging up there, there must be some sort of an error there.
perhaps you have a blank line? a value that's not a string when it's
supposed to be? a string where it's supposed to be a number?
b) search the newsgroup for "limit adding worksheets" - there's been
a lot of posts on that subject where somebody has tried to do this
50-60 times (or more) & it stops after a certain #. the consensus
seems to be that it's your computer (RAM?) memory that's getting full.
c) i don't know why closing & re-opening it won't work........ did you
start from name #1 again, or name #20? maybe you need to have a
refedit box where you can choose which row to start on & only have it
loop thru 15 @ a time or so.......
hth!
susan

 

[Guest]

Hi Susan,

Thanks again for your help. You have no idea of the optics of this project!
It's a daunting task for a finance guy... Looping through 15 names at a time
would be optimal. Would you know how to do this? I tried this two weeks ago
without success. I'm aware of this "Copy After Error" in Excel. There's a
good post of it on the Knowledge Base area on Microsoft
(http://support.microsoft.com/default.aspx?scid=kb;en-us;210684), but I was
unsuccessful in implementing the Workaround solution. How could I loop
through 10 to 15 names and then begin again after a save, close, etc. from
where it left off so that the macro won't hang up?

Thanks!
Kent.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Susan]

i'm no guru, so you might have to ask tom or bob or garry's student or
somebody else for help.............

basically you'd have to have the loop only go for 15 times - search
the newsgroup for "number of loops" for tips on how to do that.
you can have excel save & close by itself, but i think you'd have to
open it up again........ (UNLESS you put the code in an entirely
different workbook, & then repeatedly open & close the one you're
adding tabs to..... that might work).
when you open it again, @ the beginning of the code put a userform
with a refedit control on it (search the newsgroup for "userform in
loop" & "refedit controls") that will let you select which row to
start on - you could potentially have it tell you which row it last
stopped on.

i know i haven't given you ANY code on HOW to do all this, cuz as i
said i'm no guru........ i could probably do it but it would take me
hours to figure it all out! searching the newsgroup is the best &
quickest way as you can "steal" bits of code that do each piece & then
work on combining them all together.
(unless some understanding guru will write it all FOR you, but then
you won't learn how to do it!).

or, here, two brains are better than one.......... you search for
"userform in loop" & i'll search for some refedit
code.................
susan

 

[Guest]

Thanks Susan! You're amazing! I'll search for the first topic. - Kent.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Susan]

this is some basic code behind a userform that contains a refedit
box..............

Option Explicit

Sub userform1_initialize()

With Me
..refStartRange.Value = ""

..refStartRange.SetFocus
End With

End Sub


Private Sub cmdStop_click()

Unload Me

End Sub


Sub cmdcontinue_click() 'make a "continue" button on the userform for
this

If refStartRange.Value = "" Then
MsgBox "Please select a row from the spreadsheet." _
, vbOKOnly + vbInformation
Exit Sub
End If

MyRow = Range(refStartRange.Value).Row

HIDE (not unload) the userform, and change your range to

Set Rng = ws.Range("d" & MyRow", LastCell)
(something like that...... syntax might not be correct).
For Each cell In Rng
blah blah blah
finish what you're doing (the 15 times)

unload me
End Sub



in the beginning it would start:

Private Sub CommandButton3_Click()
OPEN THE USERFORM HERE
CONTINUE IN USERFORM CODING
then after unloading the userform it will automatically come back
here.
SAVE
CLOSE
END SUB

susan

 

[Susan]

you might not think i'm so great after i throw all this untested code
at you!!

here's some i found on running a loop a certain # of times............

Sub LoopTest()
Dim n
Dim V
Range("T21").Select
V = ActiveCell.Value
n = 0
Do Until n = V
'your code
'i think this is where you'd put
For Each cell In Rngblah blah blah

n = n + 1
Loop
End Sub

in this one it runs the # of times of the value in range T21..... you
could have that be the value of the refedit box PLUS 15.
i hope i'm not horribly confusing you OR driving you crazy OR making
you hate excel!
susan

 

[Guest]

Wow! I'll have to digest this and piece it together slowly. I'm a beginner in
using VBA code, but I'll give it my best shot. Perhaps this can re-direct my
efforts to circumvent this "Copy After Error". Again, I am so grateful for
your time. I couldn't find much in my search, but it seems that your solution
might be sufficient. Would it be possible to link a list of employees from
Word and create worksheets from this list? Would this get around the "Copy
After Error" in Excel?

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Susan]

i copied all of this into a test spreadsheet & i can work on it @ home
tonite. but see how far you can get without getting a massive brain
cramp.
adding Word into the mix will only complicate matters more!!!!

maybe by the morning i will have an almost-working solution that a
guru can help finish. 4pm here - leaving shortly.
susan

 

[Guest]

No, not at all! lol It's a welcome change from financial/corporate/political
stuff I usually do. Again, thanks - I'm eternally grateful! Hopefully, I'll
use your code successfully or go bald from pulling my hair out.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[(PeteCresswell)]

I need to name the worksheets using the concatenated
value of the name in Column D and a number in Column E.

Dunno if I've read the entire thread or not.

Has anybody broached the issue of legal worksheet names?

You can't just concatenate any old values. If the result is too long or the
result contains certain characters, your code will break.

Try naming a worksheet to some humongously-long name and the resulting error
1004 dialog will spell out the requirements.

e.g.

 

[Susan]

pete - oh. well, that's kent's problem. i'm not handling the
concatenated names, just the looping & userform. ha ha

kent - ok, here's what i've got. it's probably waaaay more
complicated than it needs to be, but it works.

add a module & name it something like "GlobalMods". (the name doesn't
matter, just so you know where all your declarations are.) in that
module paste this code:

Option Explicit

Public sTotal As Range
Public myVar As String
Public MyRow As Long
Public n As Long
Public V As Long

Public LastCell As Range
Public Rng As Range
Public cell As Range
Public ws As Worksheet

Dim lblLastTime As MSForms.Control

(personally i like to have all my declarations in one place, so i
don't forget that i haven't dimmed something somewhere along the
line.)

then add a userform. the userform design is up to you, but it must
have the following elements:
a refedit box named "refStartRange"
a command button named "cmdContinue"
a command button named "cmdCancel"
and a blank label (erase the caption in the properties box) named
"lblLastTime"

my design had a label before the refedit box that says "Please use the
box below to choose the starting row for the worksheets........" and
another one before the blank label that says "The last row that was
processed previously was:".

this is the code that goes behind the userform:

Option Explicit

Sub userform_initialize()

Set sTotal = ActiveWorkbook.Worksheets("Number").Range("b3")
myVar = sTotal.Value

lblLastTime.Caption = myVar
refStartRange.Value = ""
refStartRange.SetFocus

End Sub

Sub cmdCancel_click()

Unload Me

End Sub


Sub cmdContinue_click()

If refStartRange.Value = "" Then
MsgBox "Please select a row from the spreadsheet." _
, vbOKOnly + vbInformation
Exit Sub
End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

MyRow = Range(refStartRange.Value).Row

V = MyRow + 15
n = MyRow
Do Until n = V

'**Kent's code
' Set ws = ActiveSheet
' Set LastCell = ws.Cells(Rows.Count, "d").End(xlUp)
'' Set Rng = ws.Range("d15", LastCell) needs to be changed to next
line somehow
' Set Rng = ws.Range("d" & MyRow, LastCell)
' For Each cell In Rng
' If Not IsEmpty(cell) Then
' Set ws = Nothing
' On Error Resume Next
' Set ws = Worksheets(cell.Value)
' On Error GoTo 0
' If ws Is Nothing Then
' Sheets("Master").Copy
after:=Worksheets(Worksheets.Count)
' ActiveSheet.Name = cell.Value & "(" & cell.Offset(0,
1).Value & ")"
'
' cell.Hyperlinks.Add Anchor:=cell, _
' Address:="", _
' SubAddress:="'" & ActiveSheet.Name &
"'!A1", _
' TextToDisplay:=cell.Value
' End If
' End If
' Next
' Application.GoTo Reference:="Summary"
'**end Kent's code

'**Susan's test code
Range("d" & n).Select
ActiveCell.Interior.Color = vbYellow
'**end Susan's test code

n = n + 1
Loop

'change the label caption = V & save on hidden sheet "Number"
myVar = V - 1
sTotal = myVar

Unload Me

If MsgBox("15 worksheets have been added." _
& vbCrLf & _
vbCrLf & _
"The workbook will now save and close, ok?" _
, vbYesNo + vbQuestion) = vbYes Then
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True

Else
MsgBox "You will not be able to add additional worksheets" _
& vbCrLf & _
"until the workbook is closed and saved.", _
vbOKOnly + vbInformation
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End If

End Sub


insert a new worksheet & name it "Number". hide the worksheet.

your command button on the worksheet should have this code behind it:

Option Explicit

Private Sub CommandButton1_Click()

Load UserForm1
UserForm1.Show

End Sub

now, BEFORE you comment out my test code and uncomment your code, run
it so you can see what it does. don't bother closing & saving. now
run it again....... see the last row label change? this is so you
know where to start next.

now select all the cells that are now highlighted yellow & change the
fill back to "none" using format shading (or the paint-bucket-button).

THIS CODE WAS NOT TESTED WITH YOUR CODE. i simply used a little cell-
coloring code to test the looping stuff & userform. you will need to
change one line in your code (i am not sure of the correct syntax) to
utilize the start row that you've selected with the refedit box. so i
didn't do anything with your code except note the line that needs to
be changed.

(actually, in typing this & thinking about it, i probably could have
done the whole stupid thing without a userform & refedit box by just
using the hidden worksheet value to tell the macro on which row to
start....... oh well.......... keep in mind for future update.)(see, i
told you it was probably more complicated than it needed to be!)

cross your fingers & good luck! if you have more questions you should
probably start a new thread concerning the specific question you have.
hth
susan

 

[Guest]

Hi Susan and Pete,

If you guys are ever in Ottawa, Ontario, I'll gladly buy you beer I'm in
the process of implementing Susan's code, and I can't thank you enough for
your help. Pete, I've known for some time that there would be problems with
respect to data validation when the user inputs an employee name or agreement
no. An illegal character - i.e. "/", ":" - would generate an error when used
in a worksheet name. Is there a catch-all fix that I could implement? Perhaps
I'll wait until I get this stage of the puzzle solved and then I'll tackle
the data validation issue. Thanks Susan and Pete!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: (613) 943-9098
E-mail: (e-mail address removed)

 

[Susan]

no beer; how 'bout chocolate?
ha ha
let me know if it works, please?
thanks
susan

 

[Guest]

Hi Susan,

Your code works very well muchas gracias! Ok, here it goes.....

Some Issues:
1. Using your test code:
Excel doesn't report the correct amount of cells that have been coloured.
Regardless of how many cells are left before the end of the list and
subsequently filled in yellow after enacting the macro, there are still "15
names added" reported in the message box. For example, let's say that 1 extra
name has been added in column C, and user chooses this last row to take on a
yellow fill, the message will still read that "15 names have been added"
(when in fact only 1 name has been highlighted in yellow). Of course there is
still the issue of the "LastCell" being changed to reflect the next line
somehow, and this likely causes related problems....

2. Substituting my code for your test code:
Excel still doesn't like the line: ActiveSheet.Name = cell.Value & "(" &
cell.Offset(0,1).Value & ")"
When the macro is enacted, Excel doesn't stop at 15 names (likely due to the
"LastCell" issue above), and instead craps out pointing to the
"ActiveSheet.Name=....." line above. When I take out this concatenated
feature, the macro still runs until but stops when it runs into our "Copy
After" issue reported to be a bug in Excel. I suspect that once this
"LastCell" issue is resolved and Excel goes to the next cell, the
concatenated feature will not cause Excel to trip (and neither will the "Copy
After" issue).

I'll keep on trying.... thanks to you am much closer to resolving this
issue. How can I use the Excel "hidden worksheet value" to tell the macro on
which row to
start? Hope I'm not overstaying my welcome on this issue... 'bout those
chocolates....

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Susan]

1. that's one problem with working with a non-guru person - i didn't
code for every possibility, i just did what you wanted. i guess
in the part where it says:

V = MyRow + 15
n = MyRow
Do Until n = V

you could say

Do Until n=V Or n=LastCell.row

but as it stands right now LastCell isn't set until after this, so
you'd have to move that up with setting the V & n.....
as it is written right now it will ALWAYS do 15 rows, regardless of
what's in them, or anything else. so you could also change the line
above to

Do Until n=V Or n=""

if it's blank & you want to stop when it's blank. then you can change
the msgbox to report the actual # of rows that were handled......
you'd have to use MyRow subtracted from the current (last) value of n
& make that another variable (for instance, HowManyRows). then the
msgbox could be changed to:

If MsgBox(HowManyRows & " worksheets have been added." _

2. i can't help you with this part, you're beyond my understanding &
capabilities. sorry!

3. the macro is already using the hidden worksheet cell value - here
in the initialization sub:

Set sTotal = ActiveWorkbook.Worksheets("Number").Range("b3")
'the first time you run the sub this value is 0
'after that this value is where the macro stopped last
'time
myVar = sTotal.Value

lblLastTime.Caption = myVar

and then @ the end of the cmdContinue_click sub:

'change the label caption = V & save on hidden sheet "Number"
myVar = V - 1
sTotal = myVar

your starting row is captured by the refedit box (in case it is NOT
the row after the last row).

hope this helps. i'm truly sorry i can't help you with #2, but i can
learn while you're learning!
susan