An early Christmas present for me. Worked great! And runs 30x faster than my
other macro. I think the first one you sent would have worked also, if an
earlier portion of the Macro (not shown in this post) didn't hide rows at the
end of my table, which I think was throwing it off.

Marcus,
thank you for you're response too. That formula works great, but every time
I ran my Macro, it deleted at replace some of the source in the defined Name
creating an reference error. I'm just not good enough to figure out a
workaround.

Cheers,
Mike

"Roger Govier" wrote:

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