Thanks to all in advance for attempting to help, as it has been a long time since i had to play with VBA
There are two sections of code below, both are creating Pivot Tables into existing worksheets within the workbook.
The first section works perfectly time after time and i can run it multiple times without getting any errors. The second section is an almost identical copy leaving aside the destination is a different worksheet using a different table name. The code falls over with a run time error 5 at the point of trying o actually create the pivot table.
Any helps to resolve is much appreciated as i have another six pivot tables to put into the same workbook, mos of which read form the same data tab.
This piece works fine.
Sub RiskScoreByJournal()
'Create the Risk Score by Journal pivot table
'Declare variables to hold row and column identifiers to define source data cell range
Dim MFR As Long, MLR As Long, MFC As Long, MLC As Long
'Declare variables to hold source and destination range address
Dim MSR As String, MDR As String, MDN As String
Dim MSD As Range
'Declare variables to hold references for source and destination worksheets as well as the new Pivot Table
Dim MSW As Worksheet
Dim MDW As Worksheet
Dim MPT As PivotTable
'Set source and destination worksheets
With ThisWorkbook
Set MSW = .Worksheets("Data")
Set MDW = .Worksheets("Risk_Score_by_Journal")
End With
IRCount = 0
Set MSD = MSW.Range("A1").CurrentRegion
IRCount = MSD.Rows.Count
'Set destination cell address
MDR = MDW.Range("A10").Address(ReferenceStyle:=xlR1C1)
'Set row and column numbers that define source data cell range
MFR = 1
MLR = IRCount
MFC = 1
MLC = 32
'obtain address of source data cell range
With MSW.Cells
MSR = .Range(.Cells(MFR, MFC), .Cells(MLR, MLC)).Address(ReferenceStyle:=xlR1C1)
End With
MDN = "RSByJnl"
'Create Pivot Table cache and Pivot Table report
Set MPT = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MSW.Name & "!" & MSR).CreatePivotTable(TableDestination:=MDW.Name & "!" & MDR, TableName:=MDN)
Display More
This next section is the one with the issue where i am unable to work out the issue.
Sub JournalsLessThan50()
'Create the Journals Less Than £50 pivot table
'Declare variables to hold row and column identifiers to define source data cell range
Dim MFR As Long, MLR As Long, MFC As Long, MLC As Long
'Declare variables to hold source and destination range address
Dim MSR As String, MDR As String, MDN As String
Dim MSD As Range
'Declare variables to hold references for source and destination worksheets as well as the new Pivot Table
Dim MSW As Worksheet, MDW As Worksheet
Dim MPT As PivotTable
'Set source and destination worksheets
With ThisWorkbook
Set MSW = .Worksheets("Data")
Set MDW = .Worksheets("Journals_Under_£50")
End With
IRCount = 0
Set MSD = MSW.Range("A1").CurrentRegion
IRCount = MSD.Rows.Count
'Set destination cell address
MDR = MDW.Range("A10").Address(ReferenceStyle:=xlR1C1)
'Set row and column numbers that define source data cell range
MFR = 1
MLR = IRCount
MFC = 1
MLC = 32
'obtain address of source data cell range
With MSW.Cells
MSR = .Range(.Cells(MFR, MFC), .Cells(MLR, MLC)).Address(ReferenceStyle:=xlR1C1)
End With
MDN = "JnlUnder50"
'Create Pivot Table cache and Pivot Table report
Set MPT = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MSW.Name & "!" & MSR).CreatePivotTable(TableDestination:=MDW.Name & "!" & MDR, TableName:=MDN)
Display More
Hopefully someone will be able to assist. In both cases i have had to cut the code after the create section due to character restraints here.