Source Data in Pivot Tables
 

Every week I run a macro to create a pivot table, using source data whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="", TableName _
:="PivotTable2"

Before running the macro, I would go to the source data to change the cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending on the
last row and column for the weekly input file?

Thanks in advance,
cqc

You could use the CurrentRegion property. You could create the name using
this property or perhaps you could identify the pivot table source range
directly with this.

Bill Horton

"cqc" wrote:

Every week I run a macro to create a pivot table, using source data whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="", TableName _
:="PivotTable2"

Before running the macro, I would go to the source data to change the cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending on the
last row and column for the weekly input file?

Thanks in advance,
cqc

Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19", then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A contains a
value for every row in the data range ie no null values or blanks. The range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="", TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change the cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending on
the
last row and column for the weekly input file?

Thanks in advance,
cqc

Source Data in Pivot Tables
 

What about if i want to a new column?

"Ed Ferrero" wrote:

Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19", then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A contains a
value for every row in the data range ie no null values or blanks. The range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="", TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change the cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending on
the
last row and column for the weekly input file?

Thanks in advance,
cqc

Source Data in Pivot Tables
 

Hi Linda

Assuming you only have headers for the columns you want to use in row of the
sheet, then modify Ed's formula to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A($1:$1))

Alternatively, for a non-volatile formula
=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1! $A:$A),COUNTA(Sheet1!$1:$1))

--
Regards
Roger Govier



"linda" wrote in message
...
What about if i want to a new column?

"Ed Ferrero" wrote:

Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19", then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A
contains a
value for every row in the data range ie no null values or blanks. The
range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel
range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data
whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="",
TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change the
cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending
on
the
last row and column for the weekly input file?

Thanks in advance,
cqc

Source Data in Pivot Tables
 

Thanks Roger.

If i'm not using pivot table,then how?My SourceData should be expand as i
add new rows or new columns or both and it will update the chart.i've tried
to use the offset function but its just work for adding new row.Adding a new
column does not effect the chart created.i use OFFSET with a defined name.

date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

--
Regards,
Linda


"Roger Govier" wrote:

Hi Linda

Assuming you only have headers for the columns you want to use in row of the
sheet, then modify Ed's formula to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A($1:$1))

Alternatively, for a non-volatile formula
=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1! $A:$A),COUNTA(Sheet1!$1:$1))

--
Regards
Roger Govier



"linda" wrote in message
...
What about if i want to a new column?

"Ed Ferrero" wrote:

Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19", then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A
contains a
value for every row in the data range ie no null values or blanks. The
range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel
range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data
whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="",
TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change the
cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending
on
the
last row and column for the weekly input file?

Thanks in advance,
cqc

Source Data in Pivot Tables
 

Hi Linda
date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

Each of these ranges are only 1 column wide, so there would be no question
of adding a column.
A new column would have to have a new Name inserted, along with the
appropriate formula.

If you want your WklyTblSz table to grow automatically as you add rows OR
columns, then use
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),COUNTA($1:$1) )

After refreshing the PT, the new column would appear as new field in the
field list.
In order for any new added column to show in your PT, you would need to drag
that new field to the position required within the PT.
--
Regards
Roger Govier



"linda" wrote in message
...
Thanks Roger.

If i'm not using pivot table,then how?My SourceData should be expand as i
add new rows or new columns or both and it will update the chart.i've
tried
to use the offset function but its just work for adding new row.Adding a
new
column does not effect the chart created.i use OFFSET with a defined name.

date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

--
Regards,
Linda


"Roger Govier" wrote:

Hi Linda

Assuming you only have headers for the columns you want to use in row of
the
sheet, then modify Ed's formula to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A($1:$1))

Alternatively, for a non-volatile formula
=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1! $A:$A),COUNTA(Sheet1!$1:$1))

--
Regards
Roger Govier



"linda" wrote in message
...
What about if i want to a new column?

"Ed Ferrero" wrote:

Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19",
then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A
contains a
value for every row in the data range ie no null values or blanks. The
range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel
range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data
whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="",
TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change
the
cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted
depending
on
the
last row and column for the weekly input file?

Thanks in advance,
cqc

Source Data in Pivot Tables
 

Roger,
is it a need for me to use Pivot Table?if i just create chart using chart
wizard,is it still possible for me to use the OFFSET that you have suggest?
sorry cause my knowledge in excel is quite bad.

thanks~
--
Regards,
Linda


"Roger Govier" wrote:

Hi Linda
date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

Each of these ranges are only 1 column wide, so there would be no question
of adding a column.
A new column would have to have a new Name inserted, along with the
appropriate formula.

If you want your WklyTblSz table to grow automatically as you add rows OR
columns, then use
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),COUNTA($1:$1) )

After refreshing the PT, the new column would appear as new field in the
field list.
In order for any new added column to show in your PT, you would need to drag
that new field to the position required within the PT.
--
Regards
Roger Govier



"linda" wrote in message
...
Thanks Roger.

If i'm not using pivot table,then how?My SourceData should be expand as i
add new rows or new columns or both and it will update the chart.i've
tried
to use the offset function but its just work for adding new row.Adding a
new
column does not effect the chart created.i use OFFSET with a defined name.

date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

--
Regards,
Linda


"Roger Govier" wrote:

Hi Linda

Assuming you only have headers for the columns you want to use in row of
the
sheet, then modify Ed's formula to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A($1:$1))

Alternatively, for a non-volatile formula
=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1! $A:$A),COUNTA(Sheet1!$1:$1))

--
Regards
Roger Govier



"linda" wrote in message
...
What about if i want to a new column?

"Ed Ferrero" wrote:

Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19",
then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A
contains a
value for every row in the data range ie no null values or blanks. The
range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel
range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data
whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="",
TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change
the
cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted
depending
on
the
last row and column for the weekly input file?

Thanks in advance,
cqc