Pivot Table Refresh Not working

[lobop3]

In excel i have a sheet1 with data.
I have a pivot table in sheet2 using that data from sheet1
I go back and add rows of data to sheet1.
I manually go to the Pivot Table tab at the top -> click the options button -> click refresh. The newly added data to sheet1 does not appear.

The only way I can get the new data into the pivot table on sheet2 is to go to the Pivot Table tab at the top -> click the options button -> change source and manually change the range for the information on sheet1

I was hoping to create a macro that sits behind a button so my user can click the button after entering the data to sheet 1

Is the refresh option only for external data sources?
If so how can I refresh the pivot table on sheet2 using data from sheet1?

Any advice? Thank You =)

 

[Trevor G]

When the original pivot was created it sounds as though only the data on the worksheet was selected, as you have already done, why not go back to the pivot and change the source to extend down to well beyond the rows you have and when the refresh is selected it automatically includes the added data, the only extra thing that happens this way is all blank rows will show a Zero in the Pivot which you can filter on.

 

[NBVC]

You can also convert your source range to a Dynamic Named Range, and then reference that Name for your Pivot Table source. So that when you add/remove rows and refresh the Pivot Table, you should have all the info.

Here is how to create Dynamic Named Range:

http://www.contextures.com/xlNames01.html#Dynamic

 

[lobop3]

One more question =)
The dynamic range worked great by the way!! I'll will be using that many times.

How do I reference the dynamic range from the Pivot Table? I have tried putting just the name of the range and an = plus the name in the field for the pivot table source. My sheet went blank.

 

[NBVC]

Please see your other thread with that new question

http://www.access-programmers.co.uk/forums/showthread.php?t=257506

 

[lobop3]

I open name manager using fn+command+F3 because I don't have the tab at the top to click. After I open the name manager there is no field to set workbook. I'm using 2010 for the Mac.

Is there another place to make sure this setting is set to workbook?

It does highlight nicely even after I add a row. Just to make sure, it should highlight the entire table that I'm using for the pivot, right?

 

[NBVC]

I don't have Exel 2010 for Mac available to me.

Anyway you can post the workbook here our upload to dropbox?

I can check if it works with PC excel.

 

[lobop3]

I can't get the file up loaded and I tried to share a folder from dropbox but its asking for an email. How would you like me to get you the file?

 

[NBVC]

Can you zip it here?

Or use another free uploading site, like SkyDrive, or MediaFire, etc.

 

[lobop3]

I figured out my problem. In my Dynamic Named Range I did not include the header. That cause the pivot table to use the first row of data as the filters. Now I have a new problem to which I will start a new thread =)