 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008How to name a sheet from text in a cell in another sheet ?
I am very much a novice with using Excel, I'd prefer to be doing this in Access as that's what the data's aimed at in the long run, but the db I'm being forced to use is not mine and I can't work that out either - also work don't support Access so I can't use that to do the data transfer I'm being asked to do... oh why did I say I'd do this :( Down to the true business... I have created a workbook and am trying to name the sheets from cells in another sheet - all within the same workbook. I have been digging about the net and everything I've found that looks to be nearly right doesn't work :( Anyone out there willing to try to help me with this one ? Nick, your question is almost impossible to decipher. However, you can certainly use VBA to accomplish what your after, that is, if you're trying to programmatically rename or add sheets based off a list of values on a sheet. Please describe more clearly what you're trying to accomplish. Ross >I am very much a novice with using Excel, I'd prefer to be doing this > in Access as that's what the data's aimed at in the long run, but the [quoted text clipped - 11 lines] > > Anyone out there willing to try to help me with this one ? > Nick, your question is almost impossible to decipher. However, you can > certainly use VBA to accomplish what your after, that is, if you're trying [quoted text clipped - 20 lines] > > > Anyone out there willing to try to help me with this one ? Ok Ross, I'll try again. What I've got is a worksheet called Headers and I've put names in cells on it - that's cells B1 to B4 through to H1 to H4. I have created 24 sheets to reflect these cells and wish to name the sheets by the characters entered into these cells - I am also using the words in the cells in this Headers sheet to populate other cells in yet another sheet called Links. The info I'm putting into the cells in the other sheets is going to be reflected in the Links Sheet by having the correct cell in the linked sheets reflected in this Links one, then the data in the Links sheet is going to be used to populate the Access database so that the layout reflects what we want... As I said, it's all for this database which is not mine and massively more complex than just this... Sorry it's so complex, I do hope you follow this ? Thanks for attempting to aid me... Nick Nick This little macro will rename every sheet in the file by the list you have. It will not rename the "Headers" or "Links" sheets. HTH Otto Sub RenameExistingSheets() Dim rSheetNames As Range Dim ws As Worksheet Dim c As Long Application.ScreenUpdating = False c = 1 Sheets("Headers").Select Set rSheetNames = Range("B1:H4") For Each ws In ActiveWorkbook.Worksheets If ws.Name <> "Headers" And ws.Name <> "Links" Then ws.Name = rSheetNames(c).Value c = c + 1 End If Next ws Application.ScreenUpdating = True End Sub >> Nick, your question is almost impossible to decipher. However, you can >> certainly use VBA to accomplish what your after, that is, if you're [quoted text clipped - 46 lines] > > Nick Yes, I like the use of the range even better! Ross > Nick > This little macro will rename every sheet in the file by the list you [quoted text clipped - 65 lines] >> >> Nick On Apr 16, 8:22 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > This little macro will rename every sheet in the file by the list you [quoted text clipped - 15 lines] > Application.ScreenUpdating = True > End Sub Thanks you Otto, where do I put it ? Just off to bed at 22:00 for the first of four 05:00 alarms for work ! Shall catch up tomorrow through Google Groups... Nick Nick Make a copy of your file and do all this on the copy until you feel comfortable with it and it's doing what you want. With your file on the screen, do Alt-F11. This takes you to the VBE (Visual Basic Editor). On the left side of the VBE should be a pane labeled Project - VBA Project. If it's not there, click on View - Project Explorer. Find your file name in the Project Explorer and click on it. Do Insert - Module. This brings up a larger blank pane on the right. Paste the macro there. "X" out of the VBE to return to your sheet. Note that the macro name is RenameExistingSheets. Click on Tools - Macro - Macros. Find the name of the macro. Click on it. Click on Run. See what happens. If you feel shaky doing this, send me your file and I'll place the macro for you and maybe give you a button in the Header sheet that you can click on to run the macro. My email is moehrbachoextra@bellsouth.net. Remove the "extra" from this address. HTH Otto > On Apr 16, 8:22 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> > wrote: [quoted text clipped - 23 lines] > Shall catch up tomorrow through Google Groups... > Nick Many thanks Otto, that works - sort of... :) I was hoping that it may work after I had changed the contents of the cell and come out of it... The problem I now have is that I have Run-time error '1004' That means I have over 31 characters and there are ones that contravene the : \ / * [ ] rule for naming the sheets... I'll have to rethink the wording of the buttons - or just do it manually... Nick On 16 Apr, 23:36, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > Make a copy of your file and do all this on the copy until you feel [quoted text clipped - 41 lines] > > - Show quoted text - Nick What do you mean by "I was hoping that it may work after I had changed the contents of the cell and come out of it...". What cell? In what sheet? Do you mean you wanted the code to fire when you changed the entry in some cell? That's easy enough to do. What buttons? Otto Many thanks Otto, that works - sort of... :) I was hoping that it may work after I had changed the contents of the cell and come out of it... The problem I now have is that I have Run-time error '1004' That means I have over 31 characters and there are ones that contravene the : \ / * [ ] rule for naming the sheets... I'll have to rethink the wording of the buttons - or just do it manually... Nick On 16 Apr, 23:36, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > Make a copy of your file and do all this on the copy until you feel [quoted text clipped - 46 lines] > > - Show quoted text - On 17 Apr, 14:02, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > What do you mean by "I was hoping that it may work after I had changed [quoted text clipped - 71 lines] > > - Show quoted text - Hi Otto, sorry about that, getting mixed up between what I'm doing and trying to do... if you look back in the thread you'll see that I'm doing this instead of doing it in Access ! I have a sheet called Headers that I'm using to enter data into cells and using that to refer to in these other sheets so that I only have to alter one cell to get them all to be the same - this is the same cell that I'm hoping to use to name the different sheets... I meant cell when I typed button, it's going to end up being a button in a form in Access when I've finished... I'm stuck to using Excel to prove the layout and procude another list in another sheet of this book which I'll be using to update the Access table eventually...................................... oh why do I let myself in fot this sort of thing ! :) :( Nick Up until now I thought I understood what you wanted. But now, I don't know. You say: "I have a sheet called Headers that I'm using to enter data into cells and using that to refer to in these other sheets so that I only have to alter one cell to get them all to be the same - this is the same cell that I'm hoping to use to name the different sheets...". I thought that you had a range of cells, B1:H4, with a text entry in each cell. That you wanted a bunch of your existing sheets named the names in this list, one name per sheet. What do you mean by "so that I only have to alter one cell to get them all to be the same"?? Perhaps it would be better if you just write down a step-by-step procedure of how you would do this if you were doing it manually. Leave nothing out! Otto On 17 Apr, 14:02, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > What do you mean by "I was hoping that it may work after I had changed [quoted text clipped - 76 lines] > > - Show quoted text - Hi Otto, sorry about that, getting mixed up between what I'm doing and trying to do... if you look back in the thread you'll see that I'm doing this instead of doing it in Access ! I have a sheet called Headers that I'm using to enter data into cells and using that to refer to in these other sheets so that I only have to alter one cell to get them all to be the same - this is the same cell that I'm hoping to use to name the different sheets... I meant cell when I typed button, it's going to end up being a button in a form in Access when I've finished... I'm stuck to using Excel to prove the layout and procude another list in another sheet of this book which I'll be using to update the Access table eventually...................................... oh why do I let myself in fot this sort of thing ! :) :( On 17 Apr, 21:33, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > Up until now I thought I understood what you wanted. But now, I don't [quoted text clipped - 112 lines] > > - Show quoted text - Otto, sorry for the mix up as you say I got it totally wrong in what I was wanting... I am hoping that when I alter any of the cells B1-H4 in this Headers sheet, that it will name the sheets from 3 onwards - the first 2 are set as Links & Headers. Does that explain it better ? I think as usual my poor way of putting what I want across is letting me down and I'll just leave it that I have to remember to change the sheet names manually... Nick Nick Here are 2 macros that must both be in your file. Between the 2 of them, they will rename all the sheets (except Headers and Links sheets) to the names in B1:H4 of the Headers sheet. The trigger that will fire these macros is a change in the content of any cell in the B1:H4 range. You have already placed the RenameExistingSheets macro correctly and I haven't changed it from what I sent you before. But the other macro (the first macro below) is different in that it MUST be placed in a different kind of module. That macro is a sheet macro and MUST be placed in the sheet module of the Headers sheet. You can access that module by right-clicking on the Headers sheet tab and selecting View Code. Paste that macro into that module. "X" out of the VBE to return to your sheet. Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Not Intersect(Target, Range("B1:H4")) Is Nothing Then Call RenameExistingSheets End If End Sub Sub RenameExistingSheets() Dim rSheetNames As Range Dim ws As Worksheet Dim c As Long Application.ScreenUpdating = False c = 1 Sheets("Headers").Select Set rSheetNames = Range("B1:H4") For Each ws In ActiveWorkbook.Worksheets If ws.Name <> "Headers" And ws.Name <> "Links" Then ws.Name = rSheetNames(c).Value c = c + 1 End If Next ws Application.ScreenUpdating = True End Sub On 17 Apr, 21:33, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > Up until now I thought I understood what you wanted. But now, I don't [quoted text clipped - 123 lines] > > - Show quoted text - Otto, sorry for the mix up as you say I got it totally wrong in what I was wanting... I am hoping that when I alter any of the cells B1-H4 in this Headers sheet, that it will name the sheets from 3 onwards - the first 2 are set as Links & Headers. Does that explain it better ? I think as usual my poor way of putting what I want across is letting me down and I'll just leave it that I have to remember to change the sheet names manually... Nick On 18 Apr, 16:28, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > Here are 2 macros that must both be in your file. Between the 2 of [quoted text clipped - 176 lines] > > - Show quoted text - Otto, thank you. I've been very busy today and only just got chance to have a go... I've followed your instructions and the about which I've added into the Module of the Headers sheet is returning a Runtime Error 1004 All I've typed in is National Lines When I debug it highlights this ws.Name = rSheetNames(c).Value I wish I knew what I was doing so that I didn't have to trouble you ! Many thanks again... :) Nick Nick There is a problem with the code I wrote for you. If none of your sheets are named any of the sheet names in B1:H4, and you type in a sheet name in that range, the code will work fine. Otherwise you will get an error because the code is trying to name a sheet a name that already exists with another sheet, so I have to do some rewrite of the code. Tell me this about how you use this file. If the entries in B1:H4 are to be sheet names, is it that you change one of the names in B1:H4? Is that what you do? If so then do you want just the one name that you replaced changed in the sheet that had the old name? I will add an error trap in the code to pick up on the fact that a name in B1:H4 is already assigned to one of the sheets and the code will not try to rename a sheet by that name. Write back and tell me a bit more about how you use this file as regards the sheet names and B1:H4. If you wish, send me your file via email and we'll work it out with emails. My email is moehrbachoextra@bellsouth.net. Remove the "extra" from this address. Identify yourself as NickTheBatMan so I know who you are. HTH Otto On 18 Apr, 16:28, "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote: > Nick > Here are 2 macros that must both be in your file. Between the 2 of [quoted text clipped - 189 lines] > > - Show quoted text - Otto, thank you. I've been very busy today and only just got chance to have a go... I've followed your instructions and the about which I've added into the Module of the Headers sheet is returning a Runtime Error 1004 All I've typed in is National Lines When I debug it highlights this ws.Name = rSheetNames(c).Value I wish I knew what I was doing so that I didn't have to trouble you ! Many thanks again... :) Nick > Nick > There is a problem with the code I wrote for you. If none of your [quoted text clipped - 224 lines] > Many thanks again... :) > Nick Otto, very many thanks for all your time and effort with this, I see it is massively more complex than I first realised. As I have the sheets already named and it is going to be much more complex to get your scripts working under the current way it all works I shall forget the plan and just change the names manually. Again very many thanks for your efforts in helping me with this. Nick Nick You're welcome. Thanks for the feedback. Otto >> Nick >> There is a problem with the code I wrote for you. If none of your [quoted text clipped - 264 lines] > > Nick It would be better if all the names were in one column instead of spread across A-H, but try something like this using VBA. I'm making some assumptions here that might be wrong, such as: 1) The first sheet you need to rename is already named Sheet2 (presumably, Sheet1 is the one you changed to "Headers"). 2) You want to name the sheets the entire value that's in each cell. If not, parse out what you do want to use. 3) That you have, indeed, already created the additional sheets. Otherwise, I would have used code to add each sheet as needed. This might not be exactly what you need, but will hopefully lead you in the right direction. private sub NameSheets dim X as integer, SheetName as string X = 1 'For col A do while X < 5 SheetName = Sheets("Headers").range("A" & x).value Sheets(x + 1).name = sheetname X = X + 1 loop X = 1 'For col B do while X < 5 SheetName = Sheets("Headers").range("B" & x).value Sheets(x + 5).name = sheetname X = X + 1 loop X = 1 'For col C do while X < 5 SheetName = Sheets("Headers").range("C" & x).value Sheets(x + 10).name = sheetname X = X + 1 loop etc. end sub >> Nick, your question is almost impossible to decipher. However, you can >> certainly use VBA to accomplish what your after, that is, if you're [quoted text clipped - 46 lines] > > Nick
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.