Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: VBA - How to call a new tab with variable name

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - How to call a new tab with variable name

    Hi,

    I've written some code in VBA which creates a new tab based on a cell value. As you can imagine this cell value can change.

    After creating this tab i wanted to paste some information into it. However, I cannot call the tab; i'm getting error 9 subscript out of range. Or Object required depending on how i tweak the code

    Code:
    Sub Save_data2()
    'Copy A Range of Data
      Worksheets("ROI - Post Visit").Range("J30").Copy
      
    'PasteSpecial Values Only
      Worksheets("ROI - Post Visit").Range("S2").PasteSpecial Paste:=xlPasteValues
     
    Dim TabName As Range
    Set TabName = Worksheets("ROI - Post Visit").Range("S2")
    
    
    'Creat New Tab with name of property
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = TabName
    
    
    Dim NewSheet As Worksheet
    Set NewSheet = Worksheets("ROI - Post Visit").Range("S12").Value
    
    
    'copy worksheet
    Worksheets("ROI - Post Visit").Range("D2:R34").Copy
    
    
    'Paste
    Worksheets("NewSheet").Range("D2:R34").Paste
    Worksheets("NewSheet").Range("D2:R34").Copy
    Worksheets("NewSheet").Range("D2:R34").PasteSpecial Paste:=xlPasteValues
    Last edited by PGD15; Aug 26th, 2019 at 08:10 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Try
    Code:
    Set NewSheet = Sheets(tabname)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Hi Fluff, Been awhile! I get "type mismatch error"

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Ok, how about
    Code:
    Set NewSheet = Sheets(TabName.Value)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Aug 2016
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    I get subscript out of range on my Worksheets("NewSheet").Range("D2:R34").Paste line

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    324
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Hi try to change to
    Code:
    Dim TabName As String
      TabName = Worksheets("ROI - Post Visit").Range("S2")

  7. #7
    Board Regular
    Join Date
    Aug 2016
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Hi Mohadin,

    I get invalid qualifier. Using Fluff's method it's picked up but i just can't call the tab when it comes to pasting the data into it.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Replace
    Code:
    'copy worksheet
    Worksheets("ROI - Post Visit").Range("D2:R34").Copy
    
    
    'Paste
    Worksheets("NewSheet").Range("D2:R34").Paste
    Worksheets("NewSheet").Range("D2:R34").Copy
    Worksheets("NewSheet").Range("D2:R34").PasteSpecial Paste:=xlPasteValues
    with
    Code:
    NewSheet.Range("D2:R34").Value = Worksheets("ROI - Post Visit").Range("D2:R34").Value
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Aug 2016
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    Bingo, it works! How would I replicate that to copy the column sizes and formatting?

    Also side note is it possible to check all current tabs to see if the tab name is already taken. If so how would I do this?

    Ie if cell s2 says "tab1" then i try to create a new tab called "tab1" could i have a msgbox pop up informing using this is invalid.

    Ie before this line "Sheets.Add(After:=Sheets(Sheets.Count)).Name = TabName" could I call a sub to do the above mentioned validation else do the copy+paste bit.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VBA - How to call a new tab with variable name

    How about
    Code:
    Sub PGD15()
       Dim TabName As String
       Dim NewSheet As Worksheet
    
       With Worksheets("ROI - Post Visit")
          .Range("S2").Value = .Range("J30").Value
          TabName = .Range("S2").Value
       End With
       If Evaluate("isref('" & TabName & "'!A1)") Then
          MsgBox "Sheets exists"
          Exit Sub
       End If
       Sheets.Add(, Sheets(Sheets.Count)).Name = TabName
       Set NewSheet = Sheets(TabName)
       Worksheets("ROI - Post Visit").Range("D2:R34").Copy
       With NewSheet.Range("D2:R34")
          .PasteSpecial xlPasteFormats
          .PasteSpecial xlPasteValues
          .PasteSpecial xlPasteColumnWidths
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •