Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PivotTableWizard help with VB
Hi,
Using a .vbs script, the following works: -------------------------- Set xl = CreateObject("Excel.Application") xl.Workbooks.Add : xl.Visible = TRUE : xl.Sheets.Add.Name = "acc3" xl.Sheets.Add.Name = "Stats" xl.Sheets("acc3").Select xl.ActiveSheet.PivotTableWizard SourceType = xlDatabase,xl.Range ("B1:B"&nRec),"Stats!R2C1","acc3" xl.ActiveSheet.PivotTables("acc3").PivotFields _ ("Names").Orientation = 1 xl.ActiveSheet.PivotTables("acc3").PivotFields _ ("Names").Orientation = 4 xl.ActiveSheet.PivotTables("acc3").PivotFields("Na mes").AutoSort 2,"Names" -------------------------- Now, I want to place that code in a commandButton on the Excel (2002) itself. Everything works up to that point: Set xl = Application 'New code xl.Sheets("acc3").Select xl.Sheets("acc3").Select ' ***** FAILS FROM HE ***** xl.ActiveSheet.PivotTableWizard SourceType = xlDatabase, xl.Range ("B1:B" & nRec), "Stats!R2C1", "acc3" xl.ActiveSheet.PivotTables("acc3").PivotFields _ ("Names").Orientation = 1 xl.ActiveSheet.PivotTables("acc3").PivotFields _ ("Names").Orientation = 4 xl.ActiveSheet.PivotTables("acc3").PivotFields("Na mes").AutoSort 2, "Names" nRec has a value: (36000), xlDatabase is: 1, ActiveSheet seems okay... Any idea why? Thanks. |