Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table creation error VBA XLS
Hi,
I have this following error : "Unable to get the PivotTables property of the Worksheet class" on the With statement when I launch this macro. What did I forget to declare ? "Exploit" is the sheet on which I want the pivot table like this: Quest_EXT 3 Quest_ITV 3 Quest_LXE 1 Quest_KMQ 2 "Questions" is the sheet of data like this: COLUMN A date_week Quest_EXT Quest_EXT Quest_ITV Quest_EXT Quest_ITV Quest_LXE Quest_ITV Quest_KMQ Quest_KMQ MACRO : Sub dataexploitboard() Worksheets("Exploit").Activate Myfile = "tryagain.xls" mytable = "Pivot" ActiveWindow.ScrollWorkbookTabs Sheets:=-1 Sheets("Questions").Select Columns("A:A").Select destinationtable = "'[" & Myfile & "]Exploit'!R9C1" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Questions!A:A").CreatePivotTable TableDestination:= _ destinationtable, _ TableName:=mytable, DefaultVersion:=xlPivotTableVersion10 With ActiveSheet.PivotTables(mytable).PivotFields("date _week") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _ mytable).PivotFields("date_week"), "Count of date_week", xlCount ActiveWorkbook.ShowPivotTableFieldList = False Range("C11").Select ActiveCell.FormulaR1C1 = "=RC[-1]" Range("C12").Select ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]" Range("C12").Select Selection.AutoFill Destination:=Range("C12:C39") End Sub EXTRA-BALL : Please do you know how to get the number of items on rows of a pivot table ? In my exemple, the result would be 4. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table creation from another pivot table in 2007 | Excel Discussion (Misc queries) | |||
Pivot Table - there is an error message pop out during creation. | Charts and Charting in Excel | |||
Pivot table creation in excel | Excel Worksheet Functions | |||
pivot table creation procedures | Excel Discussion (Misc queries) | |||
pivot table creation in shared worksheet | Excel Discussion (Misc queries) |