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

Hi

You could do this without the use of vba. A dynamic range which covers
both rows and columns will work.

Insert - Name - Define
Place the following code in the formula bar and call your named range
eg Dataset.

=OFFSET(Summary!$A$1,0,0,COUNTA(Summary!$A:$A),COU NTA(Summary!$1:$1))

Now in your pivot table set the range to =Dataset.

Now as the rows and colums grow vertically and horizontally you are
covered.

Good luck

Marcus

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