Pivot Table - Code Optimization

[kaon]

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 ), and I would
like to seek help from all of you.

Q1:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
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!

 

[kaon]

one more question:

Q1:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase
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

 

[Dave Peterson]

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]

Thanks, Dave!