CODEWITHSUNDEEP
vbaexcel
Arayau
Arayau

Reputation: 35

Excel to create pivot table using variables

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?

Upvotes: 1

Views: 1001

Answers (1)

teylyn
teylyn

Reputation: 35915

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), _
...

Upvotes: 2

Related Questions