VBA add new sheet to End of Workbook
Results 1 to 4 of 4

Thread: VBA add new sheet to End of Workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA add new sheet to End of Workbook

    Hi all, I have a question

    Here is the code to add a new sheet having the name of a textbox.value from a userform

    Code:
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add.Name = textbox.value
    I would like to know why when the code is activated, the macro creates 2 sheets, the 1st rename with the textbox value and an other one called ''sheet1"

    I just would like a new sheet created with my textbox value

    Any idea ?

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,830
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA add new sheet to End of Workbook

    Your code actually contains instructions to create two sheets. The first line creates a sheet, adds it to the end of the workbook, and gives it a default name. The second line creates another sheet, adds it before the active sheet since you haven't specified where to place it, and then names the sheet using the textbox value.

    To create a single sheet, place it at the end of the workbook, and naming it using the textbox value, try...

    Code:
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = TextBox1.Value
    Or, alternatively...


    Code:
    With Sheets
        .Add(After:=.Item(.Count)).Name = TextBox1.Value
    End With
    Hope this helps!

  3. #3
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA add new sheet to End of Workbook

    I would like to know why when the code is activated, the macro creates 2 sheets
    Because you are using Sheets.Add twice
    Either


    Code:
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = textbox.value

    or

    Code:
    Sub AddSht()
     Worksheets.Add(After:=Sheets(Sheets.Count)).Name = textbox.value
    End Sub

    Where textbox is the actual name of your textbox.


    Edit: should have refreshed my screen then would have seen the previous post
    Last edited by MARK858; Aug 14th, 2019 at 04:39 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Apr 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA add new sheet to End of Workbook

    Hi @Domenic and @MARK858

    Many thanks for your reply on that matter.

    It works !

    Kind regards

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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