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

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

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

You're very welcome. Thanks for the feedback letting us all know it
worked.

--
Regards

Roger Govier


"Spidey" wrote in message
...
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

Reading through this thread was very useful in that I have a similar
situation. It does differ slightly in that I have three worksheets that act
as the data source, sort of like "summary1", "summary2", "summary3".
"Summary1" provides data to four different pivot tables within the workbook.
"Summary2" provides data to three pivot tables and "summary3" provide data to
two pivot tables. All of this resides in the same workbook. The summary
worksheets get updated weekly from an Access export so using the dynamic name
range formula did not work because it was wiping out the OFFSET formula after
each worksheet was export to this file. I'm really interested in seeing how
the multiple worksheet and pivot table code would be setup. I'm pretty new at
this...and so am very appreciative of any help. Thank you...you guys are
geniuses.

"Roger Govier" wrote:

You're very welcome. Thanks for the feedback letting us all know it
worked.

--
Regards

Roger Govier


"Spidey" wrote in message
...
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