Auto. Update Pivot Table Range Macro

One way is to create a dynamic range, and use that as the data source for the pivot table. Assuming your table starts in A1 in Sheet1:
Insert | Name | Define
Top row, insert a name, eg PivotRange
Bottom row, this formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-1,n) where n is the # of columns in the table.
Click Add, then OK
Go to the Pivot Table, then in the wizard, change the data source to =PivotRange

You still need to refresh the pivot table when new data is added, but no need to adjust the range - that expands automatically.

If you want to have a macro that does the refresh, record one and use a button to trigger it. Post back if you need more details.

Denis

Dennis,

Gave your response a try and all went simmingly except that your solution inexpicably (or my error) will not capture but two of my three entries when I update. Thoughts?

Neglected to tell you I am using a Data Form to enter new records; I doubt that would nullify your answer but I leave it to you to tell me.

isadoko

Hi isadoko,

Check to see whether there any blanks in Column A. If there are, and you will ALWAYS have the same number of blank cells, you can adjust the formula for the range as follows --
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A) + m, n) where n is the # of columns in the table and m is the # of blanks in Column A

Also, I made an error -- had -1 after the COUNTA expression, that shouldn't be there if you use the range for a pivot table.

Denis[/i]

Dennis,

Followed your instruction and it worked. However can you briefly explain the blanks. When I entered a value for 'm' (chose 1) I noticed that my range was exceeded by 1 row and generated a blank entry in the table. Is that what you meant or have I got it wrong? I ask this for I have no blank fields in column A save for those exceeding my table's range. I remedied the blanks by deselecting them in the affected field. That correct?

isadoko

Hi isadoko, probably best if I take the OFFSET function apart for you.

Current formula is
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A) + m , n )
Syntax is
=OFFSET(Top left of range, offset rows, offset cols, height (rows), width (cols))
IN our case, Sheet1!A1 is the top of the range, os that is the first parameter. The next 2 parameters are 0 because we don't want to mave the start position.
The COUNTA function works out the height of the table, by counting all non-blank cells in Column A. The last parameter is width in columns.
If your data entry form is giving you blanks in column A, you may have something in one of the other columns -- a formula that fills down? Don't know but that's my guess

Hope that helps

Denis

Denis,

Got it!...I think. I made m=o and all my problems evaporated. First know that my sheets is layed out thus:

Column Headings: A-M
Data Rows: 2-740 and counting

Column A must never be blank for there I enter my location number and I must have a location number. I posited that my blank was being generated by my m=1 factor so I replaced the 1 with a zero and magically all worked perfectly. Naturally I must not forget location, location, location. I appreciate your help and patience.

No worries -- glad to help!

Denis