Consulting

Results 1 to 4 of 4

Thread: Change Sheet name

  1. #1
    VBAX Regular
    Joined
    Aug 2017
    Posts
    17
    Location

    [Solved] Change Sheet name

    Hello
    I am using Excel 2010.
    I'm wanting to add several sheets to my workbook and I believe Excel needs to know the current sheet name to add more sheets.
    I may be wrong. The problem is that every time I run my program the sheet name changes. So, it's kind of a moving target. I tried
    Dim shName As String
    Dim currentName As String
            ThisWorkbook.Sheets(currentName).Name = shName  ' error here
            currentName = ActiveSheet.Name
            shName = InputBox("What name you want to give for your sheet")
            ThisWorkbook.Sheets(currentName).Name = shName
    And I can input a name but I get the error "Subscript out of range"
    A typical name for the sheet could be V_PMs_Due_0823_133421.
    I should add that I am importing data from another source an in house maintenance reporting program. As soon as I
    import the data the sheet name changes from sheet1 to V_PMs_Due_0823_133421 or something like it.
    Also the other two sheets disappear, Sheet2 and Sheet3.
    Doesn't seem like I can capture the sheet name to change it.

    Is there another approach to adding more sheets?
    Thanks for any help. I hope I was clear enough in my question.
    Last edited by strato; 08-23-2017 at 01:31 PM.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,333
    Location
    Try

    Activesheet.Name = "My New Name"
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Aug 2017
    Posts
    17
    Location
    That worked nicely - thank you!

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = InputBox("new sheet name")
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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