Pivot table through VBA - Syntax issue

[plix]

Hello,

In the code below, the pre-defined range "ActRange" seems to be creating a problem:

<font face=Courier New><SPAN style="color:#007F00">' Find the LAST row</SPAN><br>LastRow = ActiveSheet.Cells.find(What:="*", _<br>SearchDirection:=xlPrevious, _<br>SearchOrder:=xlByRows).Row<br><br><SPAN style="color:#007F00">' Find the LAST column</SPAN><br>LastCol = ActiveSheet.Cells.find(What:="*", _<br>SearchDirection:=xlPrevious, _<br>SearchOrder:=xlByColumns).Column<br><br><SPAN style="color:#00007F">Dim</SPAN> MyWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> ActRange <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> MyWS = ActiveSheet<br><SPAN style="color:#00007F">Set</SPAN> ActRange = MyWS.Range(Cells(1, 1), _<br>    Cells(LastRow, LastCol))<br><br><SPAN style="color:#007F00">'Create pivot table 3</SPAN><br>ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _<br>        "'" & ActiveSheet.Name & "'" & ActRange).CreatePivotTable TableDestination:="", _<br>        TableName:="PivotTable3"</FONT>
...

Seems there must be something wrong with the syntax but cannot find what it is.
Thanks for any help,

Plix

 

[Andrew Poulsom]

Maybe:

ActRange.Address

 

[RoryA]

ActRange is a range, not an addresss string. Use:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'" & ActiveSheet.Name & "'" & ActRange
.Address(referencestyle:=xlR1C1).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable3"

 

[plix]

Rorya,

The following gave me an "invalid procedure call or argument" error:

<font face=Courier New><SPAN style="color:#007F00">' Find the LAST row</SPAN><br>LastRow = ActiveSheet.Cells.find(What:="*", _<br>SearchDirection:=xlPrevious, _<br>SearchOrder:=xlByRows).Row<br><br><SPAN style="color:#007F00">' Find the LAST column</SPAN><br>LastCol = ActiveSheet.Cells.find(What:="*", _<br>SearchDirection:=xlPrevious, _<br>SearchOrder:=xlByColumns).Column<br><br><SPAN style="color:#00007F">Dim</SPAN> MyWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> ActRange <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> MyWS = ActiveSheet<br><SPAN style="color:#00007F">Set</SPAN> ActRange = MyWS.Range(Cells(1, 1), _<br>    Cells(LastRow, LastCol))<br><br><SPAN style="color:#007F00">'Create pivot table 3</SPAN><br>ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _<br>        "'" & ActiveSheet.Name & "'" & ActRange.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable TableDestination:="", _<br>        TableName:="PivotTable3"</FONT>
...

Any idea?

Thanks again,

Plix

 

[RoryA]

Sorry, missed out the exclamation mark:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'" & ActiveSheet.Name & "'!" & ActRange.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable3"

 

[plix]

Rorya,

Thanks a lot for your help, it works beautifully.

Plix