VBA Pivot Tables SourceData:= Questions

drozek

Board Regular
Joined
Aug 3, 2011
Messages
67
Hi folks,

I am creating a pivot table, i used the macro recorder to do this. But when there is more or less data, I either get blanks or some data doesn't get selected. Is there a way to tell the SourceData to get only the visible data through the 13 columns?


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R790C13", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6


I ran the same report this morning and I have 788 rows, so my pivot table would create some blanks.

Thank you!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
If i have formed this correctly then

Code:
Sub test()
Dim LR As Long
LR = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LR & "C13", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
End Sub
 
Upvote 0
If i have formed this correctly then

Code:
Sub test()
Dim LR As Long
LR = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LR & "C13", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
End Sub

I am getting a Run-time error '13':
Type mismatch

I add a sheet before this active pivot:

Sub bycustomerpivot()
' This module to be used after all the customer numbers are converted and all blanks are removed. ANd customer are changed
' bycustomerpivot Macro
'
Sheets.Add
' ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
' "Data!R1C1:R790C13", Version:=6).CreatePivotTable TableDestination:= _
' "Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Dim LR As Long
LR = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LR & "C13", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
 
Upvote 0
If i have formed this correctly then

Code:
Sub test()
Dim LR As Long
LR = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LR & "C13", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
End Sub

That would produce an mismatch error as LR doesnt produce an integer. If D was the column to check then its:

Code:
LR = Sheets("Data").Range("D" & Sheets("Data").Rows.Count).End(xlUp).Row
 
Upvote 0
I am taking all the data from my "Data" tab and create a large pivot table on this new sheet.
 
Upvote 0
I got one more question for you @steve the fish

How do I do something similar, I need to select the range, 5th line of code below to select all the cells based on text in Col A?

Sheets.Add After:=ActiveSheet
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "by Size"
Sheets("by Customer").Select
Range("A3:N100").Select
Range("L3").Activate
Selection.Copy
Sheets("by Size").Select
ActiveSheet.Paste Link:=True


Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Sheets("by Size").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"by Size!R1C1:R100C14", Version:=6).CreatePivotTable TableDestination:= _
"Sheet5!R3C1", TableName:="byCustomer", DefaultVersion:=6
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top