Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Code Optimization
Hi all,
First of all, I have to thank all of you reading this thread. Moreover, I have done a google search on pivottable tutorial with nothing returns. I am weak in coding with PivotTable (as well as others :P), and I would like to seek help from all of you. Q1: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:="Sheet1!R3C3:R261C21" _ ).CreatePivotTable TableDestination:="", tablename:="PivotTable3" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) a) How can I merge this 2 lines into one? b) Can I use Range object as sourcedata coz I was warned? Q2: Dim pTable as PivotTable Set pTable = ActiveSheet.PivotTables("PivotTable3") a) Is it useful to declare such statement? Will it simplify the code further? Q3: With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Project Stage") ..Orientation = xlColumnField ..Position = 1 End With With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Country") ..Orientation = xlRowField ..Position = 1 End With With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Product Type") ..Orientation = xlRowField ..Position = 2 End With With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Project Stage") ..Orientation = xlColumnField ..Position = 1 End With With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Project Stage") ..Orientation = xlDataField ..Position = 1 End With a) They look redundnant. Anyway to simplify them? Q4: Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) ..LineStyle = xlContinuous ..Weight = xlMedium ..ColorIndex = 1 End With With Selection.Borders(xlEdgeTop) ..LineStyle = xlContinuous ..Weight = xlMedium ..ColorIndex = 1 End With With Selection.Borders(xlEdgeBottom) ..LineStyle = xlContinuous ..Weight = xlThin ..ColorIndex = 1 End With With Selection.Borders(xlEdgeRight) ..LineStyle = xlContinuous ..Weight = xlMedium ..ColorIndex = 1 End With Selection.Borders(xlInsideVertical).LineStyle = xlNone a) Same as Q3. (I remember there is a constant for highlighting all border but I cannot recall at this moment) THANKS! --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Code Optimization
one more question:
Q1: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase SourceData:="Sheet1!R3C3:R261C21" _ ).CreatePivotTable TableDestination:="", tablename:="PivotTable3" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) How can I change to R1C1 style from activesheet.UsedRange? SourceData:="Sheet1!R3C3:R261C21" Thanks -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Code Optimization
dim mySource as range
set mysource = sheet1.usedrange ...., _ SourceData:=mySource.address(external:=true), ... ... But I'd be a little careful with that .usedrange. Excel keeps track of the usedrange a lot closer than you do (I bet). Try hitting ctrl-end and see where it takes you. But if you want to use C3:U (lastrow of column C), you could do this: with sheet1 set mysource = .range("c3:u" & .cells(.rows.count,"C").end(xlup).row) end with "kaon <" wrote: one more question: Q1: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:="Sheet1!R3C3:R261C21" _ ).CreatePivotTable TableDestination:="", tablename:="PivotTable3" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) How can I change to R1C1 style from activesheet.UsedRange? SourceData:="Sheet1!R3C3:R261C21" Thanks. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Code Optimization
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table formula optimization Question | Excel Discussion (Misc queries) | |||
Excel code optimization | Excel Discussion (Misc queries) | |||
Pivot Table Code | Excel Discussion (Misc queries) | |||
vba code for Pivot Table | Excel Programming | |||
HELP! Code for Pivot Table | Excel Programming |