Excel to create pivot table using variables

Question

I have a workbook with 10 sheets. I tried to automate the creation of pivot table on each sheets through a macro. I recorded the macro for the first sheet called Timetables

Below are part of the scripts:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _        
ActiveWorkbook.Connections("WorksheetConnection_Timetables!$A$1:$D$382"), _
Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:= _
"Timetables!R1C5", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion15

Since each range of data to be used is different on each sheet. I tried to create a variable for that range.

Dim ws as Worksheet
Set ws = Activesheet

Dim wr As Range
Set wr = Range("A1", Range("D1").End(xlDown))

The question is how can I replace the range Timetables!$A$1:$D$382 with the variable on the script?

teylyn · Accepted Answer · 2017-12-19 01:43:46Z

2

You don't need a range, you need a text string as the argument for the pivot table source data parameter.

Dim lastrow As Long
Dim ws As Worksheet
Dim TheRange As String

Set ws = ThisWorkbook.Sheets("WorksheetConnection_Timetables")

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
TheRange = "WorksheetConnection_Timetables!$A$1:$D$" & lastrow

Then you can use that as the data source parameter:

..._
ActiveWorkbook.Connections(TheRange), _
...

answered Dec 19, 2017 at 1:43

teylyn

36.3k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arayau Over a year ago

Thanks Teylyn. I created text string variables as you suggested. I tested and it work now. Excellence!

Collectives™ on Stack Overflow

Excel to create pivot table using variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related