VBA code to dynamically change a formula under Command Text under Queries & Connections for a Pivot table

Question

I would like a VBA code that allows me to dynamically change a formula's range to the last row of data for a pivot table. The formula is stored in Command Text under Definition under Connection Properties in Queries & Connections. Sub UpdatePivotTableDataSource() Dim ws As Worksheet Dim pivotSheet As Worksheet Dim lastRow As Long Dim conn As WorkbookConnection Dim cmdText As String Dim pt As PivotTable
enter image description here
The current code I attached has a Run-time error 1004 at line: "conn.OLEDBConnection.CommandText = cmdText"
Thanks in advance!

' Set the worksheet and find the last row of data in "Billing Analysis " sheet
Set ws = ThisWorkbook.Sheets("Billing Analysis ")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row

' Update the CommandText in the connection properties
cmdText = "'Billing Analysis '!$A$1:$AA$" & lastRow

' Locate the connection for the Pivot Table and update the command text
For Each conn In ThisWorkbook.Connections
    If conn.Name = "WorksheetConnection_Billing Analysis !$A$1:$bw$30000" Then ' Adjust to your connection's exact name
        conn.OLEDBConnection.CommandText = cmdText
        Exit For
    End If
Next conn

' Refresh PivotTable5 on "No. of Billings-by Service" sheet
Set pt = ThisWorkbook.Sheets("No. of Billings-by Service").PivotTables("PivotTable5")
pt.RefreshTable

MsgBox "PivotTable5 has been updated with the new data range and refreshed!"

End Sub

I expect the code to dynamically change the formula range of the pivot table to include up to the last row of data in a scenario that the datasource overshoots the current range.

Michal · Accepted Answer · 2024-11-16 03:32:38Z

This seems like a very awkward way of using VBA to complicate a very simple task. Why not use an Excel Table or a named range instead?

Both methods ensure that the Pivot Table always includes all data without requiring a manual range update or VBA code.

1. Using an Excel Table

Select your entire data range (Billing Analysis sheet, columns A to AA).
Press Ctrl + T to convert it into an Excel Table.
Give your table a name (e.g., BillingData).
When creating the Pivot Table, use BillingData as the source.

2. Using a Dynamic Named Range

If you prefer not to use an Excel Table, you can create a dynamic named range.

Go to Formulas > Name Manager > New.
Enter the following:
- Name: BillingDataRange
- Refers to:
```
=OFFSET('Billing Analysis '!$A$1, 0, 0, COUNTA('Billing Analysis '!$A:$A), 27)
```
- Here, COUNTA('Billing Analysis '!$A:$A) finds the last row based on column A, and 27 is the total number of columns (A to AA).
Use BillingDataRange as the source for your Pivot Table.

This approach is much simpler and avoids potential issues with VBA, especially with connection properties that can lead to run-time errors.

Collectives™ on Stack Overflow

VBA code to dynamically change a formula under Command Text under Queries & Connections for a Pivot table

1 Answer 1

1. Using an Excel Table

2. Using a Dynamic Named Range

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1. Using an Excel Table

2. Using a Dynamic Named Range

Comments

Your Answer

Sign up or log in

Post as a guest

Related