Dynamic pivot range without a table

Question

Is there a way to change the range of a Pivot table using a macro, without having to use a table or named range?

I recorded a macro where I changed the range (see code below). But I was wondering if I could incorporate this statement - Range("A2", Range("A2").End(xlToRight).End(xlDown)).Select - into the source, so that it always selects the entire range of data regardless of how many rows there are?

I tried to replace the characters after Sheet1 in the code below with

Range("A2", Range("A2").End(xlToRight).End(xlDown)).Select

but that didn't work.

I'm sure it's possible to update the source automatically without using a table (there's too much data to use a table, and the spreadsheet slows down significantly when you update the range with a table).

VBA Code:

Sub change_pivot_source_data()
'
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "C:\Users\Documents\[Change Pivot Table source.xlsb]Sheet1!R1C1:R4C3", _
        Version:=8)
End Sub

Cyrus · Accepted Answer · 2022-09-02 13:30:49Z

By specifying ActiveSheet or in my case wsPivot, the location and name of the corresponding workbook are redundant.

I have tested the code below and it works for me.


    Option Explicit
    
    Private Sub Test5()
    
    Dim sChar$, sSource$
    Dim iLastUsedCol%
    Dim iLastUsedRow&
    Dim WS As Worksheet, wsPivot As Worksheet
    
    Set WS = ThisWorkbook.Sheets("A")
    Set wsPivot = ThisWorkbook.Sheets("Sheet3")
    
    With WS
        'furthest column to right on a worksheet
        sChar = ColumnChars2(Columns.count)
        'last used header column on this sheet
        iLastUsedCol = .Range(sChar & 1).End(xlToLeft).Column
        'last used row of data on this sheet
        iLastUsedRow = .Range("A" & Rows.count - 1).End(xlUp).Row
        
        wsPivot.Activate
        
        sSource = WS.Name & "!R1C1:R" & iLastUsedRow & "C" & iLastUsedCol
        'transferring data to array
        wsPivot.PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sSource, Version:=8)
    End With
    
    End Sub

The pivot code is taken from here and I have consequently upvoted that solution.

This procedure below is called from the code above.

Public Function ColumnChars2(iCol As Variant) As String

On Error GoTo Err_Handler

'
' calculates character form of column number
'

Dim iPrePrefix As Integer, iPrefix As Integer, iSuffix As Integer

iSuffix = iCol
iPrefix = 0
Do Until iSuffix < 27
    iSuffix = iSuffix - 26
    iPrefix = iPrefix + 1
Loop
iPrePrefix = 0
Do Until iPrefix < 27
    iPrefix = iPrefix - 26
    iPrePrefix = iPrePrefix + 1
Loop
ColumnChars2 = IIf(iPrePrefix = 0, "", Chr(64 + iPrePrefix)) & IIf(iPrefix = 0, "", Chr(64 + iPrefix)) & Chr(64 + iSuffix)

Exit Function
Exit_Label:
  On Error Resume Next
  Application.Cursor = xlDefault
  Application.ScreenUpdating = True
  Application.CutCopyMode = False
  Application.Calculation = xlCalculationAutomatic
  Exit Function
Err_Handler:
  MsgBox Err.Description, vbCritical, "ColumnChars2"
  Resume Exit_Label

End Function

Collectives™ on Stack Overflow

Dynamic pivot range without a table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related