dim as long questions

[drozek]

Hi experts

I have a pivot table with values that starts in A3 to N3 and down, but the values down change depending on the week, how would I capture all of them and named them "PivotGrowth" ?

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="PivotGrowth", RefersToR1C1:= _
"='by Growth Account'!R3C1:R286C14"
Range("P4").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PivotGrowth", Version:=6).CreatePivotTable TableDestination:= _
"by Growth Account!R4C16", TableName:="PivotTable1", DefaultVersion:=6

 

[drozek]

Bumping this up

 

[RoryA]

Is there anything in the first two rows of the sheet, or are they blank?

 

[drozek]

Is there anything in the first two rows of the sheet, or are they blank?

There is data in there that doesn't get touched.

 

[RoryA]

I'd suggest something like this:

Dim PivotRange as Range
Set PivotRange = Range("A3").Currentregion
Set pivotrange = Range("A3", pivotrange.cells(pivotrange.cells.count))
pivotrange.name = "PivotGrowth"