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/