VBA - How to call a new tab with variable name

[PGD15]

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

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

 

[Fluff]

Try

Set NewSheet = Sheets(tabname)

 

[PGD15]

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

 

[Fluff]

Ok, how about

Set NewSheet = Sheets(TabName.Value)

 

[PGD15]

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

 

[mohadin]

Hi try to change to

Dim TabName As String
  TabName = Worksheets("ROI - Post Visit").Range("S2")

 

[PGD15]

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.

 

[Fluff]

Replace

'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

NewSheet.Range("D2:R34").Value = Worksheets("ROI - Post Visit").Range("D2:R34").Value

 

[PGD15]

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.

 

[Fluff]

How about

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