PDA

View Full Version : Using variable for "Name:=..."



DIYiT
06-02-2010, 03:20 PM
I'm a total beginner in VBA. I just bought a book to learn VBA for excel, and I'm trying to learn as much as I can, as quickly as I can for a project at work.

The problem. I need to run the same macro repeatedly on different pages which will be constantly changing. I want to create a named range that is unique to each sheet so that I can then use the named range to create a dynamic pivot table. However, I'm having problems creating a unique name in VBA... Here's a snippet of my problem area.

'newSht is defined by an input box, and is used to name the new sheet
Dim newSht As String

'the variable I want to use to name the Named Range
Dim dynmcRangeName As String

'This is how I'm trying to combine the newSht variable with a constant name
dynmcRangeName = newSht & "-dynmcAssyRange"

'Trying to create the new named range - the red text is where I'm having problems.
ActiveWorkbook.Worksheets(newSht).Names.Add Name:=dynmcRangeName, _
RefersToR1C1:="=OFFSET(R1C1,0,0,COUNTA(R2C1:R200C1)+1,19)"

'Trying to create the pivot table. It's worked as long as I've had the orange text as something like "=dynmcAssyRange" but I want to use the variable name.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dynmcRangeName, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="R2C21", TableName:="AssyPvt", DefaultVersion _
:=xlPivotTableVersion12

If you need more of the code base, I'd be willing to post it up, but I'm sure this is a noob problem that will be easily resolved.