Wrong number of Arguments or Invalid property assignment

Hello - great tips on this website - giving me some great ideas - dont know whether I use them right yet - still knew to this.

Below is my macro for taking a range of data in two columns, pivoting it, and replacing the original data with the pivoted data. I have had this working fine in a simpler form - but I am at present improving it to work on a variable data range!!

Problem I have atm is that I get a "Wrong number of Arguments or Invalid property assignment" compile error whereas the same code previously worked fine - any ideaS?????? i have pointed out the point 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:="'[Pivot test.xls]Sheet3'!R6C5", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Stk 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 Greg

Try
Application.Goto Reference:="List2"

--
Greetings from New Zealand
Bill K

"Turin" <[email protected]> wrote in
message news:[email protected]...
>
> Hello - great tips on this website - giving me some great ideas - dont
> know whether I use them right yet - still knew to this.
>
> Below is my macro for taking a range of data in two columns, pivoting
> it, and replacing the original data with the pivoted data. I have had
> this working fine in a simpler form - but I am at present improving it
> to work on a variable data range!!
>
> Problem I have atm is that I get a "Wrong number of Arguments or
> Invalid property assignment" compile error whereas the same code
> previously worked fine - any ideaS?????? i have pointed out the point
> 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:="'[Pivot
> test.xls]Sheet3'!R6C5", _
> TableName:="PivotTable3",
> DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Stk
> 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 Greg
>
>
> --
> Turin
> ------------------------------------------------------------------------
> Turin's Profile:
> http://www.excelforum.com/member.php...o&userid=24987
> View this thread: http://www.excelforum.com/showthread...hreadid=387864
>