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