VBA Pivot Tables SourceData:= Questions

[drozek]

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!

 

[mole999]

If i have formed this correctly then

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

 

[drozek]

If i have formed this correctly then

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

 

[steve the fish]

If i have formed this correctly then

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:

LR = Sheets("Data").Range("D" & Sheets("Data").Rows.Count).End(xlUp).Row

 

[drozek]

I am taking all the data from my "Data" tab and create a large pivot table on this new sheet.

 

[drozek]

Anyone else have any input @Fluff?

 

[steve the fish]

Did you try as suggested?

 

[drozek]

Did you try as suggested?

Oh man I am sorry I just saw what you posted. Yes this worked! THank you very much!

 

[drozek]

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