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

 

[PGD15]

Bingo x2!

I had to swap the paste values and formats due to merged cells but working like a treat many thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

I would also recommend getting rid of merged cells, they will only cause you a lot of grief & hassle.

 

[PGD15]

1 last thing sorry, is it possible to combo and past the values of a combo box.

IE cell K2 is a combo box with 2 options. Can i paste this box with both option into the new tab?

 

[Fluff]

I would suggest that you start a new thread for this.
I never use comboboxes on sheets

 

[PGD15]

Thanks, will do! Appreciate all your help.

 

[Fluff]

You're welcome & thanks for the feedback

 

[PGD15]

for anyone reading this thread, i just had to add .PasteSpecial xlPasteValidation along with the other paste options....