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
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