is there a way to control the sheet name you add

[Cindy Wang]

is there a way to control the sheet name you add? It could be called
sheet 1, sheet 2 or sheet3, etc. Is there a way to keep it same when
you add a new one? Or when you create a pivot, is there a way you
can always create from sheet1 when using a new sheet? I try to create
a macro, but could not control the sheet name and vloop would not be
able to find the sheet. Thanks,

 

[GS]

It happens that Cindy Wang formulated :

is there a way to control the sheet name you add? It could be called
sheet 1, sheet 2 or sheet3, etc. Is there a way to keep it same when
you add a new one? Or when you create a pivot, is there a way you
can always create from sheet1 when using a new sheet? I try to create
a macro, but could not control the sheet name and vloop would not be
able to find the sheet. Thanks,

This appears to be a continuation of the same problem you posted for on
May 11th. IMO, you're trying to get a fully qualified reference for a
newly added sheet that eventually contains a PivotTable. Here's an
example of one way to do this so your code uses object refs rather than
hard-code sheetnames...

Dim wksNew As Worksheet '//use object variable to ref the new sheet
Set wksNew = Worksheets.Add

Use the new sheet's name in a formula...

ActiveCell.Formula = "=VLOOKUP(RC[-2],'" & wksNew.Name _
& "'!R5C1:R3000C3,2,FALSE)"

...though, I suggest you assign defined names to the PivotTable and
LookupValueColumn, and use those instead of R1C1 notation...

ActiveCell.Formula = "=VLOOKUP(LookupValueColName,'" & wksNew.Name _
& "'!<PivotTableName>,2,FALSE)"

...where the defined name for the PivotTable is fully absolute. Make the
defined name for the LookupValue column-absolute/row-relative.

*Note* that after adding the new sheet it becomes the active sheet and
so ActiveCell refers to that sheet. To keep a ref to the sheet where
you want the formula put you need to set a ref to it *before* adding
the new sheet...

Dim wksTarget As Worksheet, wksNew As Worksheet
Set wksTarget = ActiveSheet
Set wksNew = Worksheets.Add

With wksNew
'code to build PivotTable goes here...
End With 'wksNew

wksTarget.ActiveCell.Formula = "=VLOOKUP(LookupValueColName,'" _
& wksNew.Name _
& "'!<PivotTableName>,2,FALSE)"

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Gord Dibben]

You cannot name a new sheet same as an existing sheet unless you
delete existing first.

To simply add a new sheet with a name.............

Sub insert_sheet()
Dim newSht As Worksheet
Set newSht = Worksheets.Add
newSht.Name = "newSht"
End Sub

If you want to add it again you must first delete it...............

Sub insert_sheet22()
Dim newSht As Worksheet
Dim wkSht As Worksheet
For Each wkSht In Worksheets
With wkSht
If .Name = "newSht" Then
Application.DisplayAlerts = False
Sheets("newSht").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set newSht = Worksheets.Add
newSht.Name = "newSht"
End Sub


Gord

 

[Walter Briscoe]

In message <[email protected]> of Wed, 16 May
2012 13:58:01 in microsoft.public.excel.programming, Gord Dibben

You cannot name a new sheet same as an existing sheet unless you
delete existing first.

To simply add a new sheet with a name.............

Sub insert_sheet()
Dim newSht As Worksheet
Set newSht = Worksheets.Add
newSht.Name = "newSht"
End Sub

I dare say it is a question of comfort, but that seems much less simple
than Worksheets.Add.Name = "newSht"

If you want to add it again you must first delete it...............

Sub insert_sheet22()
Dim newSht As Worksheet
Dim wkSht As Worksheet
For Each wkSht In Worksheets
With wkSht
If .Name = "newSht" Then
Application.DisplayAlerts = False
Sheets("newSht").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set newSht = Worksheets.Add
newSht.Name = "newSht"
End Sub

Are you intending this code to cater for both "newSht" existing and
absent before insert_sheet22 is called? I am more comfortable with

Sub insert_sheet22()
' Replace sheet "newSht"

Application.DisplayAlerts = False
Sheets("newSht").Delete
Application.DisplayAlerts = True

Worksheets.Add.Name = "newSht"
End Sub

or even

Sub insert_sheet22()
Const SheetName As String = "newSht" ' Replace that sheet

Application.DisplayAlerts = False
Sheets(SheetName).Delete
Application.DisplayAlerts = True

Worksheets.Add.Name = SheetName
End Sub

I am glad I checked that code. I originally wrote
Const String SheetName = "newSht" ' Replace that sheet