Pivot Range

[VBABEGINER]

Hi All,
In the red highlighted, how do place Range(A:AC)

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"\\serverpath\[ANALYSIS.xlsx]Details![COLOR=#ff0000]R1C1:R3C32[/COLOR]", Version:=xlPivotTableVersion14)

 

[VBABEGINER]

Help Appreciated

 

[SpillerBD]

Don't. You do not want to include all 1.2 Mil rows of data, even if blank.
You will be better served converting that range to a Table (CTRL+T) and then creating a Pivot Table.
Use the macro recorder to get the syntax for using a Table in the macro.

 

[VBABEGINER]

Any idea on this

'For Pivot Refresh
Dim Tbl As Range
Tbl = Range("A2", Range("AB2").End(xlDown)).Select

 

[VBABEGINER]

Hi All,
instead of starting new query...can any one genuinely guide how I can give editable range every time in pivot code...

 

[SpillerBD]

Any idea on this

'For Pivot Refresh
Dim Tbl As Range
Tbl = Range("A2", Range("AB2").End(xlDown)).Select

Go for a CurrentRegion
Example:

Set TabRange = Range("A3").CurrentRegion

 

[VBABEGINER]

So nice of you and really cool for this reply..

But, also help me, how do i write a code for this..How this name i can place in code....

Go for a CurrentRegion
Example:

Set TabRange = Range("A3").CurrentRegion

 

[SpillerBD]

I copied my use directly, but it would essentially replace yours as

Dim Tbl as Range
' Getting the actual range assigned to the Rang object
Set Tbl= Range("A3").CurrentRegion
' Then I make that range available to the name manger.
ActiveSheet.ListObjects.Add(xlSrcRange, TabRange, , xlYes).Name _
    = "Open_Acct_Balances"
' I then begin the Pivot Table Cache using the Named Range that has been added
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "[B]Open_Acct_Balances[/B]", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet1!R1C1", TableName:="APPO_Acrual_PT", DefaultVersion _
        :=xlPivotTableVersion15

Its not the same as using proper Table methodology, but in my case it wasn't needed. This macro runs as a one-and-done situation and there are no updates to the data in the Workbook.
I also wrote this before I was into the PowerQuery methodologies. Thankfully that is open to all Excel 2016 users, but also disappointed knowing there are many on 2013 or earlier

 

[VBABEGINER]

I like and happy to see your reply. But have a question here, what is "Open_Acct_Balances" in code..