-
May 9th, 2018, 04:48 AM
#1
Thread Starter
New Member
Pivot tables from VB
Hi guys,
Banging my head against the wall for days here. Found a good article for the code to generate a pivot table in a new sheet from data on a sheet. But I want to have a pivot table created from sheet 1 and sheet 2 both to be generated on sheet 3.
Although the first one works, I can't adapt it to suit what I need it to do.. Anyone able to assist?
Thanks so much
Code:
Sub ByPersonPivot()
'Create the Time report Pivot for the By Person Sheet
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Inserting the new sheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Time Report")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="ByPersonTimeReport")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(0, 0), TableName:="ByPersonTimeReport")
'Insert Row Fields
With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Name Engineer")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Hour Type")
.Orientation = xlRowField
.Position = 2
End With
'Insert Column Fields
With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Recoverable?")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Time2")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Time2"
End With
'Format Pivot
TableActiveSheet.PivotTables("ByPersonTimeReport").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonTimeReport").TableStyle2 = "PivotStyleMedium9"
End Sub
-
May 9th, 2018, 06:45 AM
#2
Re: Pivot tables from VB
if the first pivot table is created correctly, then you should be able to reuse the code to create the second
you can test this to see if it helps at all
Code:
Sub ByPersonPivot()
'Create the Time report Pivot for the By Person Sheet
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Inserting the new sheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Time Report")
Set OSheet = Worksheets("2nd sheet") ' change sheet name to suit
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="ByPersonTimeReport")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(0, 0), TableName:="ByPersonTimeReport")
'Insert Row Fields
With PSheet.PivotTables("ByPersonTimeReport")
With .PivotFields("Name Engineer")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Hour Type")
.Orientation = xlRowField
.Position = 2
End With
'Insert Column Fields
With .PivotFields("Recoverable?")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With .PivotFields("Time2")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Time2"
End With
'Format Pivot
.PivotTables("ByPersonTimeReport").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonTimeReport").TableStyle2 = "PivotStyleMedium9"
End With
' 2nd pivot table
'Define Data Range
LastRow = OSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = OSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = OSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache change target address else it will overwrite the first table
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="2nd pivot table")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(0, 0), TableName:="2nd pivot table")
'Insert Row Fields
With PSheet.PivotTables("2nd pivot table")
With .PivotFields("Name Engineer")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Hour Type")
.Orientation = xlRowField
.Position = 2
End With
'Insert Column Fields
With .PivotFields("Recoverable?")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With .PivotFields("Time2")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Time2"
End With
'Format Pivot
.ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("2nd pivot table").TableStyle2 = "PivotStyleMedium9"
End With
End Sub
i have not tested the changes i made to your code nor the additional code i put to create the 2nd table
you will probably need to change the names of all the pivot fields as well as the source worksheet and pivot table, also the formatting of pivot fields will need to be modified as required, make sure to find some blank area as the target for the 2nd table
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 9th, 2018, 06:50 AM
#3
Thread Starter
New Member
Re: Pivot tables from VB
Thanks, I will give this a shot now.
I am having some "targeting" issues with it. The first pivot generates in B2 however I need this to be A1, The 2nd pivot would be perfect in F1
-
May 9th, 2018, 07:26 AM
#4
Thread Starter
New Member
Re: Pivot tables from VB
Located the Table Destination adjustment. Copied over to a new module for the 2nd pivot as it uses a different DSheet so unable to use the same variables on the first pivot.. Having an error with the Pivot Cache now though...
Runtime 1004
Application-defined or object-defined error
Code:
Sub ByPersonPayrollPivot()
'Create the Time report Pivot for the By Person Sheet
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Define Pivot sheet and Data Sheet
Set PSheet = Worksheets("ByPerson")
Set DSheet = Worksheets("Payroll")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
TableName:="ByPersonPayroll")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
'Insert Row Fields
With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("Engineer")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("BASIC")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Basic"
End With
'Insert Data Field
With ActiveSheet.PivotTables("ByPersonpayroll").PivotFields("Total OT")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Total OT"
End With
'Format Pivot
TableActiveSheet.PivotTables("ByPersonPayroll").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonPayoll").TableStyle2 = "PivotStyleMedium9"
End Sub
-
May 9th, 2018, 07:58 AM
#5
Re: Pivot tables from VB
i am not sure what is causing your problem, but it would appear that you are trying to create 2 pivot tables at the same cell, which could possibly be an issue
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
TableName:="ByPersonPayroll")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
both lines of code appear to create a pivot table from the same pivot cache, at the same location
did you try creating the second pivot table before the original, just to see if it works that way?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 9th, 2018, 08:07 AM
#6
Thread Starter
New Member
Re: Pivot tables from VB
Yeah deleted the destination sheet then recreated before running the 2nd Pivot and get the error I described in my last reply.
Can a second pivot cache be created or can we refresh/delete the old one when the 2nd macro is run?
The location of the pivot tables should be (1, 1) and (1, 9) respectively so plenty of space without them overlapping
-
May 9th, 2018, 04:12 PM
#7
Re: Pivot tables from VB
can you post some sample workbook (zip first), that demonstrates the problem, for testing?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 10th, 2018, 02:20 AM
#8
Thread Starter
New Member
Re: Pivot tables from VB
TestExcelPivots.zip
I've attached it now. I put the pivot table design in a tab so its clear what I'm aiming to achieve pivot table wise.
Thanks very much for your help.
-
May 10th, 2018, 10:21 AM
#9
Thread Starter
New Member
Re: Pivot tables from VB
I don't suppose anyone had a chance to take a look at this? Been sat trying all day again today but not getting anywhere.
Thanks in advance.
-
May 10th, 2018, 04:20 PM
#10
Re: Pivot tables from VB
no chance last night, computer playing up
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 11th, 2018, 02:40 AM
#11
Thread Starter
New Member
Re: Pivot tables from VB
Feels so close.. Doing it in 2 separate macros which are almost the same but with the data source, rows/columns adjusted, the first pivot generates fine, when running the 2nd macro to create it in the same sheet it generates the table but brings back the mismatch runtime error (see attached image). Problem with the code in the debugger is highlighted below..
Code:
Sub ByPersonPayrollPivot()
'Create the Payroll Pivot for the By Person Sheet
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Inserting the new sheet
ActiveSheet.Name = "ByPerson"
Set PSheet = Worksheets("ByPerson")
Set DSheet = Worksheets("Payroll")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
TableName:="ByPersonPayroll")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
'Insert Row Fields
With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("Engineer")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("BASIC")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Basic"
End With
'Insert Data Field
With ActiveSheet.PivotTables("ByPersonpayroll").PivotFields("Total OT")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "##.##"
.Name = "Sum of Total OT"
End With
'Format Pivot
TableActiveSheet.PivotTables("ByPersonPayroll").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonPayoll").TableStyle2 = "PivotStyleMedium9"
End Sub
-
May 11th, 2018, 07:20 AM
#12
Re: Pivot tables from VB
i tested your code as in module 2, the type mismatch is because a pivot table is created and you are trying to assign it to a pivotcache object, assign to ptable rather than pcache
the line of code afterwards will also not work, is not needed at all and should be removed, trying to create a 2nd copy of the pivot table, as pointed put in post 5
it would appear if only one pivot table is on a worksheet it just ignores the errors, but a 2nd pivot on a worksheet must do it all correctly
it may also help to explicitly delete any pivot table by name, before creating a new one of that name
personally i would try to avoid all references to activesheet (or active anything), use the ptable object to work with the fields etc
the last line also has errors, probably should be
Code:
'Format Pivot
ActiveSheet.PivotTables("ByPersonPayroll").ShowTableStyleRowStripes = PivotStyleMedium9
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 11th, 2018, 07:50 AM
#13
Thread Starter
New Member
Re: Pivot tables from VB
Thank you so much for taking the time to look at it. How do I assign to ptable rather than pcache? Also which 2nd line of code?
Sorry- I'm fairly inexperienced with this stuff...
-
May 11th, 2018, 04:39 PM
#14
Re: Pivot tables from VB
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
TableName:="ByPersonPayroll")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
change to
Code:
'Define Pivot Cache
Set PTable = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
TableName:="ByPersonPayroll")
'Insert Blank Pivot Table
' Set PTable = PCache.CreatePivotTable _
' (TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|