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:=xlDatab ase
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

--
Turi
-----------------------------------------------------------------------
Turin's Profile: http://www.excelforum.com/member.php...fo&userid=2498
View this thread: http://www.excelforum.com/showthread.php?threadid=38786

Try
Application.Goto Reference:="List2"

--
Greetings from New Zealand
Bill K

"Turin" wrote in
message ...

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:=xlDatab ase,
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