I'm realtively new to using Macros. I've searched high and low, but found only two relavant sights pertaining to my problem:
1) http://support.microsoft.com/kb/940166
2) http://social.answers.microsof…4f-4e87-be75-af468b97ae69
My code is still failing though. I'm highlighting (red writing) where it fails. I'm also including the original piece of code that represents how I would like it too look ideally (I've included piece I used to do debugging. Said code has a lot of superfluous junk for troubleshooting).
Please help...and, Thank you ahead of time.
Code
Option Explicit
Sub PTable(ByVal strWorkSheetName As String, _
ByVal strPivotTableName As String, _
ByVal strDataSource As String, _
ByVal strAnchorCell As String)
'A Bunch of debugging code
Debug.Print "Active sheet name entering from CMDL Runner: " & Activesheet.Name
Debug.Print "strWorkSheetName entering from CMDL Runner: " & strWorkSheetName
Debug.Print "Query Table to be pivoted: " & Activesheet.ListObjects(1).Name
Debug.Print "strDataSource entering from CMDL Runner: " & strDataSource
Debug.Print "strPivotTableName entering from CMDL Runner: " & strPivotTableName
Debug.Print "strAnchorCell entering from CMDL Runner: " & strAnchorCell
'code to set the pivotTable name to generic, subsequent number
' (not needed, used this to weed out problem but it didn't work)
Dim intCount As Integer
Sheets(strWorkSheetName).Activate
Range(strAnchorCell).Select
intCount = Activesheet.PivotTables.Count
'more debugging code
Debug.Print "IntCount is: " & intCount
Debug.Print "Pivot Table number is: " & "PivotTable" & intCount + 1
'Start of PivotTable Creation
[COLOR=red]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/COLOR]
[COLOR=red] strDataSource, Version:= _[/COLOR]
[COLOR=red] xlPivotTableVersion12).CreatePivotTable _[/COLOR]
[COLOR=red] TableDestination:=Worksheets(strWorkSheetName).Range(strAnchorCell), _[/COLOR]
[COLOR=red] TableName:="PivotTable" & intCount + 1, _[/COLOR]
[COLOR=red] DefaultVersion:=xlPivotTableVersion12[/COLOR]
ActiveWorkbook.ShowPivotTableFieldList = True
Activesheet.PivotTables(strPivotTableName).AddDataField Activesheet.PivotTables( _
strPivotTableName).PivotFields("TestsQtysExcludeDup"), "Sum of TestsQtysExcludeDup" _
, xlSum
With Activesheet.PivotTables(strPivotTableName).PivotFields("TypeNumber")
.Orientation = xlRowField
.Position = 1
End With
With Activesheet.PivotTables(strPivotTableName).PivotFields("OperDesc")
.Orientation = xlRowField
.Position = 2
End With
With Activesheet.PivotTables(strPivotTableName).PivotFields("OperDispCode")
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
And this is ideally what I'd like it to look like (main difference in Green)
Code
Option Explicit
Sub PTable(ByVal strWorkSheetName As String, _
ByVal strPivotTableName As String, _
ByVal strDataSource As String, _
ByVal strAnchorCell As String)
Sheets(strWorkSheetName).Activate
Range(strAnchorCell).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
strDataSource, Version:= _
xlPivotTableVersion12).CreatePivotTable _
TableDestination:=Worksheets(strWorkSheetName).Range(strAnchorCell), _
[COLOR=yellowgreen]TableName:=strPivotTableName[/COLOR], _
DefaultVersion:=xlPivotTableVersion12
ActiveWorkbook.ShowPivotTableFieldList = True
Activesheet.PivotTables(strPivotTableName).AddDataField Activesheet.PivotTables( _
strPivotTableName).PivotFields("TestsQtysExcludeDup"), "Sum of TestsQtysExcludeDup" _
, xlSum
With Activesheet.PivotTables(strPivotTableName).PivotFields("TypeNumber")
.Orientation = xlRowField
.Position = 1
End With
With Activesheet.PivotTables(strPivotTableName).PivotFields("OperDesc")
.Orientation = xlRowField
.Position = 2
End With
With Activesheet.PivotTables(strPivotTableName).PivotFields("OperDispCode")
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
BTW, The error I'm getting is the '1004': Application-defined or Object-defined error.
Thanks again.