VBA - How to call a new tab with variable name

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
132
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,487
Office Version
365
Platform
Windows
Try
Code:
Set NewSheet = Sheets(tabname)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,487
Office Version
365
Platform
Windows
Ok, how about
Code:
Set NewSheet = Sheets(TabName.Value)
 

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
132
I get subscript out of range on my Worksheets("NewSheet").Range("D2:R34").Paste line
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
359
Office Version
2013
Platform
Windows
Hi try to change to
Code:
Dim TabName As String
  TabName = Worksheets("ROI - Post Visit").Range("S2")
 

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
132
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,487
Office Version
365
Platform
Windows
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
 

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
132
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,487
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,078,393
Messages
5,339,926
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top