T
Turin
Hello - great tips on this website - giving me some great ideas - don
know whether I use them right yet - still knew to this.
Below is my macro for taking a range of data in two columns, pivotin
it, and replacing the original data with the pivoted data. I have ha
this working fine in a simpler form - but I am at present improving i
to work on a variable data range!!
Problem I have atm is that I get a "Wrong number of Arguments o
Invalid property assignment" compile error whereas the same cod
previously worked fine - any ideaS?????? i have pointed out the poin
below where the problem starts!!!
Sub Pivot_V5()
'
' Pivot_V5 Macro
' Macro recorded 17/07/2005 by Greg Sheriston
'
'
ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:= _
"=OFFSET(Sheet3!R1C1,4,0,COUNTA(Sheet3!C1),2)"
ActiveWorkbook.Names.Add Name:="List2", RefersToR1C1:= _
"=OFFSET(Sheet3!R1C1,5,0,COUNTA(Sheet3!C1),2)"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase
SourceData:="List"). _
CreatePivotTable TableDestination:="'[Pivo
test.xls]Sheet3'!R6C5", _
TableName:="PivotTable3"
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="St
no"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Val").Orientation
_
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("List2").Select <-----THIS BIT
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("E8:F8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("E8:F32").Select
Selection.Copy
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
ANY ideas greatly appreciated - thanks in advance Gre
know whether I use them right yet - still knew to this.
Below is my macro for taking a range of data in two columns, pivotin
it, and replacing the original data with the pivoted data. I have ha
this working fine in a simpler form - but I am at present improving i
to work on a variable data range!!
Problem I have atm is that I get a "Wrong number of Arguments o
Invalid property assignment" compile error whereas the same cod
previously worked fine - any ideaS?????? i have pointed out the poin
below where the problem starts!!!
Sub Pivot_V5()
'
' Pivot_V5 Macro
' Macro recorded 17/07/2005 by Greg Sheriston
'
'
ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:= _
"=OFFSET(Sheet3!R1C1,4,0,COUNTA(Sheet3!C1),2)"
ActiveWorkbook.Names.Add Name:="List2", RefersToR1C1:= _
"=OFFSET(Sheet3!R1C1,5,0,COUNTA(Sheet3!C1),2)"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase
SourceData:="List"). _
CreatePivotTable TableDestination:="'[Pivo
test.xls]Sheet3'!R6C5", _
TableName:="PivotTable3"
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="St
no"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Val").Orientation
_
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("List2").Select <-----THIS BIT
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("E8:F8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("E8:F32").Select
Selection.Copy
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
ANY ideas greatly appreciated - thanks in advance Gre