The macro I want to create is to create a pivot table from data that will
come from a .csv file. Currently we can't even get the macro to work when the
data is present in the Worksheet.
I have tried recording the macro and using macros I have sourced from
others. Each time it works the first time and then never works again. It
always stops at the identical code in each different macro.
This is the code where I get the Run-time error '5' Invaild procedure call
or argument.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
Here is the full code
Sub dataexploitboard()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "C:\q_ndr_sb.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
wsExploit.PivotTables(mytable).AddDataField wsExploit.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
With wsExploit.PivotTables(mytable).PivotFields("date_week")
.Orientation = xlRowField
.Position = 1
End With
With wsExploit.PivotTables("Pivot").PivotFields("date_week")
.Orientation = xlDataField
.Calculation = xlRunningTotal
.BaseField = "date_week"
.Name = "Total"
End With
With wsExploit.PivotTables("Pivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
Any help would be greatly appreciated.
Susan
Bookmarks