|
Programatically Naming Worksheets (using catenated values)
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 |
Programatically Naming Worksheets (using catenated values)
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 |
Programatically Naming Worksheets (using catenated values)
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 |
Programatically Naming Worksheets (using catenated values)
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 - |
Programatically Naming Worksheets (using catenated values)
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 |
Programatically Naming Worksheets (using catenated values)
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 - |
Programatically Naming Worksheets (using catenated values)
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 - |
Programatically Naming Worksheets (using catenated values)
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 On Mar 12, 3:16 pm, klysell .(donotspam) wrote: 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 -- Hide quoted text - - Show quoted text - |
Programatically Naming Worksheets (using catenated values)
Thanks Susan! You're amazing! I'll search for the first topic. - Kent.
-- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Susan" wrote: 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 On Mar 12, 3:16 pm, klysell .(donotspam) wrote: 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 -- Hide quoted text - - Show quoted text - |
Programatically Naming Worksheets (using catenated values)
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 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 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() 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) OPEN THE USERFORM HERE CONTINUE IN USERFORM CODING then after unloading the userform it will automatically come back here. SAVE CLOSE END SUB susan On Mar 12, 3:24 pm, "Susan" wrote: 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 On Mar 12, 3:16 pm, klysell .(donotspam) wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Programatically Naming Worksheets (using catenated values)
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 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 blah 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 On Mar 12, 3:39 pm, "Susan" wrote: 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 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 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() 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) OPEN THE USERFORM HERE CONTINUE IN USERFORM CODING then after unloading the userform it will automatically come back here. SAVE CLOSE END SUB susan On Mar 12, 3:24 pm, "Susan" wrote: 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 On Mar 12, 3:16 pm, klysell .(donotspam) wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Programatically Naming Worksheets (using catenated values)
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" wrote: 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 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 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() 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) OPEN THE USERFORM HERE CONTINUE IN USERFORM CODING then after unloading the userform it will automatically come back here. SAVE CLOSE END SUB susan On Mar 12, 3:24 pm, "Susan" wrote: 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 On Mar 12, 3:16 pm, klysell .(donotspam) wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Programatically Naming Worksheets (using catenated values)
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 On Mar 12, 3:54 pm, klysell .(donotspam) wrote: 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 |
Programatically Naming Worksheets (using catenated values)
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 "Susan" wrote: 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 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 blah 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 On Mar 12, 3:39 pm, "Susan" wrote: 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 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 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() 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) OPEN THE USERFORM HERE CONTINUE IN USERFORM CODING then after unloading the userform it will automatically come back here. SAVE CLOSE END SUB susan On Mar 12, 3:24 pm, "Susan" wrote: 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 On Mar 12, 3:16 pm, klysell .(donotspam) wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Programatically Naming Worksheets (using catenated values)
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 |
Programatically Naming Worksheets (using catenated values)
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 |
Programatically Naming Worksheets (using concatenated values)
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 |
Programatically Naming Worksheets (using concatenated values)
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 |
Programatically Naming Worksheets (using concatenated values)
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 On Mar 13, 1:46 pm, klysell .(donotspam) wrote: 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- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 09:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com