Hi

It worked fine for me.
However, it is possible that the lastcell is somewhere outside the range
of your table, and thereby including columns without a column header,
which the PT will not allow.

Try changing to this instead. Again it worked fine for me
Dim mySource As Range, mcol As Long, mrow As Long
With Worksheets("summary")
mrow = Cells(.rows.Count, "A").End(xlUp).Row
mcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set mySource = .Range(.Cells(1, 1), .Cells(mrow, mcol))

End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=mySource.Address(external:=True)).Crea tePivotTable _
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

End Sub

I am running on XL2003

--
Regards

Roger Govier


"Spidey" wrote in message
...
Roger,
Thank you for you help, but there seems to be something missing with
my
syntax. It is returning an error about the information being in a
list (I
can run a pivot table outside of the Macro with no problems as well as
the
old macro which doesn't provide the column dynamic range). Any
additional
thoughts...my Macro is below.

Thanks again,
Mike
---------------------------------------------------------
Dim mySource As Range, rng2 As Range
With Worksheets("summary")
Set rng2 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Set mySource = .Range(.Cells(1, 1), rng2)
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=mySource.Address(external:=True)).Crea tePivotTable _
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
sub end
__________________________________________________ ____
"Roger Govier" wrote:

Hi

One way

Dim mySource As Range, rng2 as Range
With Worksheets("summary")
Set rng2 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Set mySource = .Range(.Cells(1, 1), rng2)
End With


--
Regards

Roger Govier


"Spidey" wrote in message
...
Hello! I am writing a Macro to generate a pivot table with a
dynamic
rows
and columns, as it will be used to run pivots for many different
worksheets
in different workbooks (by people that are not excel experts like
me...insert
laugher here!).

As such the following macro runs nicely (my thanks to an earlier
post
by
Dave Peterson), and appears to be selecting the dynamic range for
the
number
of rows correctly. However, my columns may also vary, and I have
tried to no
avail to include this variable.

Any help would be greatly appreciated.

My Macro:

Sub PivotTable ()

Dim mySource As Range
With Worksheets("summary")
Set mySource = .Range("A1:AD" & .Cells(.Rows.Count, "A").Row)

End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=mySource.Address(external:=True)).Crea tePivotTable _
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

End Sub