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 -