Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use cell value as sheet name (2003)

    My boss changed his mind. instead of creating a new file he wants a new sheet with the name from one of the cells created. I managed to get Excel to create the new sheet, copy over all the info from the old sheet, delete the info entered on the old sheet, but cannot get the name of the new sheet to equal Range(D3:E3). (these are merged cells)
    ...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use cell value as sheet name (2003)

    You must refer to the first cell in a merged range, so use Range("D3").

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use cell value as sheet name (2003)

    Right, now that is working but I cannot copy sheet1 to the new sheet because the name is a variable. I put in:

    Sheets("D3").Select

    D3 is the cell that I grabbed to name the new sheet. Once I select that sheet I want to paste all the information from Sheet1. Here is the total of my script so far:

    Sheets("Grading").Select
    Sheets.Add.Name = Range("D3")
    Sheets("Grading").Select
    Cells.Select
    Selection.Copy
    Sheets("D3").Select
    ActiveSheet.Paste
    ...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use cell value as sheet name (2003)

    Sheets("D3").Select

    is trying to select a sheet literally named "D3". If you want to select the sheet that is named what the value in RAnge D3 is, try:
    Sheets(Range("D3").value).Select

    Does this code do what you want (warning "aircode") without all the selecting?

    dim wGrade as worksheet
    dim wks as worksheet
    set wGrade = Sheets("Grading")
    set wks = Sheets.Add
    wks.Name = wgrade.Range("D3").value
    wgrade.Cells.Copy wks.range("A1")


    Steve

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use cell value as sheet name (2003)

    Steve,
    That worked great! a little fiddling with the rest of my macro and all is almost running smoothly. Just one more tiny issue. This sheet is setup as protected so that users can tab from one entry field to another, but I want to change the order of the tabbed fields. Any thoughts?

    Doug
    ...

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use cell value as sheet name (2003)

    After the user fills in the last field they should then hit 'Tab' and be resting on the 'Submit' button. That would be perfect, but does not have to happen that way.

    Doug
    ...

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use cell value as sheet name (2003)

    I don't think there is a way to do this in excel directly:

    You could put the cells themselves in the proper order so the tabs go as desired

    Instead of editing in the worksheet "proper", you could create a userform for data entry and set the objects in the form as desired. When the user wants to add/edit/delete anything it would go thru the userform...

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use cell value as sheet name (2003)

    1)Add the submit button
    Add unprotected cells next in the sequence
    You could have a selectionchange event to "trap" when this unprotected cells (or cells) is selected and have the code put the focus on the submit button

    2) as mentioned earlier, you could add a userform...

    Steve

  9. #9
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use cell value as sheet name (2003)

    Thanks gang! Everything is working to perfection! (or close enough for government work anyway)
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •