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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710


"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

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



"Gary''s Student" wrote:

Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710


"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

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

this should work
susan

On Mar 12, 10:38 am, klysell .(donotspam) wrote:
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



"Gary''s Student" wrote:
Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710

"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211- Hide quoted text -

- Show quoted text -

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



"klysell" wrote:

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



"Gary''s Student" wrote:

Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710


"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

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



On Mar 12, 2:24 pm, klysell .(donotspam) wrote:
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



"klysell" wrote:
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

"Gary''s Student" wrote:

Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710

"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211- Hide quoted text -

- Show quoted text -

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...;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" wrote:

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



On Mar 12, 2:24 pm, klysell .(donotspam) wrote:
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



"klysell" wrote:
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

"Gary''s Student" wrote:

Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710

"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211- Hide quoted text -

- Show quoted text -

Per klysell .(donotspam):
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.
-----------------------------------
Sheets("x").Select
Sheets("x").Name = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx"
-----------------------------------


So you'll probably want to code a "WorksheetName_Legal()" function to shoehorn
your concatenations into those requirements.
--
PeteCresswell

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!) :D

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





On Mar 12, 7:28 pm, "(PeteCresswell)" wrote:
Per klysell .(donotspam):

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.
-----------------------------------
Sheets("x").Select
Sheets("x").Name = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx"
-----------------------------------

So you'll probably want to code a "WorksheetName_Legal()" function to shoehorn
your concatenations into those requirements.
--
PeteCresswell

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:


"Susan" wrote:

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!) :D

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





On Mar 12, 7:28 pm, "(PeteCresswell)" wrote:
Per klysell .(donotspam):

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.
-----------------------------------
Sheets("x").Select
Sheets("x").Name = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx"
-----------------------------------

So you'll probably want to code a "WorksheetName_Legal()" function to shoehorn
your concatenations into those requirements.
--
PeteCresswell

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

On Mar 13, 11:54 am, klysell .(donotspam) wrote:
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:



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

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" wrote:

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

On Mar 13, 11:54 am, klysell .(donotspam) wrote:
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:



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