Debra Dalglish
 

Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?

1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:
Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Thanks a lot for your help.

For query 2,

What about if instead of the amount field, I have calculated field?


"Debra Dalgleish" wrote:

1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:
Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I'm not sure what you're trying to do, but if you want a calculation
that uses an employee count, you could add a column (EmpCount) to the
source data, and enter a 1 in each row. In the pivot table, divide
Amount by EmpCount.

nc wrote:
Debra Thanks a lot for your help.

For query 2,

What about if instead of the amount field, I have calculated field?


"Debra Dalgleish" wrote:


1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:

Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra

For query 1.
Can I use it to the refreshall keyword to refresh all the pivot tables in
the workbook.


Why a copy of the same pivottable pasted in another worksheet won't autofit
the first column of the pivottable whereas the original does? I have tried
everything, autoformat and Preserve fomatting.




"Debra Dalgleish" wrote:

I'm not sure what you're trying to do, but if you want a calculation
that uses an employee count, you could add a column (EmpCount) to the
source data, and enter a 1 in each row. In the pivot table, divide
Amount by EmpCount.

nc wrote:
Debra Thanks a lot for your help.

For query 2,

What about if instead of the amount field, I have calculated field?


"Debra Dalgleish" wrote:


1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:

Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Yes, you can use RefreshAll. Did you have a problem with it?

I don't know why it's not autofitting the column. You could try copying
the pivot table, and pasting it onto a different sheet. Then, choose
EditPaste Special, Column Widths.

nc wrote:
Hi Debra

For query 1.
Can I use it to the refreshall keyword to refresh all the pivot tables in
the workbook.


Why a copy of the same pivottable pasted in another worksheet won't autofit
the first column of the pivottable whereas the original does? I have tried
everything, autoformat and Preserve fomatting.




"Debra Dalgleish" wrote:


I'm not sure what you're trying to do, but if you want a calculation
that uses an employee count, you could add a column (EmpCount) to the
source data, and enter a 1 in each row. In the pivot table, divide
Amount by EmpCount.

nc wrote:

Debra Thanks a lot for your help.

For query 2,

What about if instead of the amount field, I have calculated field?


"Debra Dalgleish" wrote:



1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:


Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra

Refresh all works.

I have tried your suggestion regarding auto fitting but no luck. Have you
by an chance encountered the same problem or know of others that have?

Thanks.

"Debra Dalgleish" wrote:

Yes, you can use RefreshAll. Did you have a problem with it?

I don't know why it's not autofitting the column. You could try copying
the pivot table, and pasting it onto a different sheet. Then, choose
EditPaste Special, Column Widths.

nc wrote:
Hi Debra

For query 1.
Can I use it to the refreshall keyword to refresh all the pivot tables in
the workbook.


Why a copy of the same pivottable pasted in another worksheet won't autofit
the first column of the pivottable whereas the original does? I have tried
everything, autoformat and Preserve fomatting.




"Debra Dalgleish" wrote:


I'm not sure what you're trying to do, but if you want a calculation
that uses an employee count, you could add a column (EmpCount) to the
source data, and enter a 1 in each row. In the pivot table, divide
Amount by EmpCount.

nc wrote:

Debra Thanks a lot for your help.

For query 2,

What about if instead of the amount field, I have calculated field?


"Debra Dalgleish" wrote:



1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:


Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html