VBA for Pivot Table problem

[thelostscott]

Hi all,

I'm having an issue trying to create a Pivot Table in VBA. How do I get the red section of the code below to reflect a changing array, not a set array as it is showing?

I want to be able to use this Macro in other workbooks where the Source Data will change.

Sheets("SOURCE").Range("A1").Select
    
    Range(Selection, Selection.End(xlToRight)).Select
    
    Range(Selection, Selection.End(xlDown)).Select
    
    Sheets.Add
    
[COLOR=Black]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, [COLOR=Red]SourceData:="SOURCE!R1C1:R26523C46"[/COLOR] Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12[/COLOR]

Thanks

 

[RoryA]

You can use something like:

Dim strAddress as string

   strAddress = "SOURCE!" & sheets("SOURCE").Range("A1").currentregion.address(referencestyle:=xlr1c1)
    Sheets.Add
    
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strAddress,  Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12

or use a dynamic named range.

 

[Misca]

Try

Dim Rng As Range
Set Rng = Range("A1").CurrentRegion
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=Range("A3"), TableName:="PT1", DefaultVersion:=xlPivotTableVersion12

 

[thelostscott]

Thanks for that! rorya, it didn't seem to like that for some reason but I plugged in Misca's coding and it worked a treat! Thanks very much!

 

[RoryA]

Worked fine for me as long as you have a SOURCE sheet and the new sheet is called Sheet2. If the target sheet is wrong you would get an error so it would be better to specify the sheet name.
Note: in my experience, if you have large data sources, using R1C1 referencing is better than A1 or range objects.