Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to control the sheet name you add
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to control the sheet name you add
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to control the sheet name you add
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 On Wed, 16 May 2012 08:51:04 -0700 (PDT), Cindy Wang wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to control the sheet name you add
In message of Wed, 16 May
2012 13:58:01 in microsoft.public.excel.programming, Gord Dibben writes 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 ;) Gord On Wed, 16 May 2012 08:51:04 -0700 (PDT), Cindy Wang wrote: 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, -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get Control of a Butoon placed in the Sheet | Excel Programming | |||
Control in Chart Sheet | Charts and Charting in Excel | |||
Control Sheet | Excel Worksheet Functions | |||
Control on different sheet than the executable | Excel Programming | |||
For each control in sheet | Excel Programming |