VBA Pivot table refresh issues

[esmecat]

Hi. I am having issues with a pivot table refresh.
I have a n excel template which contains a pivot table. Periodically I paste new data into a worksheet and run a macro to format the data, do some calculations and then refresh the pivot table on another worksheet. This macro all runs fine. After I have added some notes to the data I then click again to manually refresh the data and get the following error:

On start up my data source for the pivot table is ''Data Tab'!$B:$AQ'
My data is pasted into 'Data Tab' columns A to AL
When I run the macro its adds some extra columns and my data range ends up as 'Data Tab' columns A to AQ but the data source for the pivot table has now changed to 'Data Tab'!$B:$AR.
There is nothing in column AR and this isn't used through the whole process.
Any idea where this extra column on my data source is coming from and how I can stop it?

Thanks

 

[FatBoyClam]

Is your macro inserting a column between B and AQ?

The Pivot table definition will automatically adjust out by one column (from AQ to AR) if you insert a column between it's first and last columns.

This looks to be what is happening. It's therefore my guess that this new column is not given a value in the first row, which the Pivot Table interprets as headers, and all columns in a Pivot Table definition must have a header value, or you get the error you've posted a screenshot of.

Cal

 

[esmecat]

Hi. Thanks for the reply.
It starts off with less columns - A to AL and then inserts one and adds a few on the end to end up at AQ but never extends to AR and then back down if that makes sense?

 

[FatBoyClam]

Hi.

When a column is inserted (e.g. Add an extra column at AA), then it 'pushes' all the columns to the right of that along (AA->AB, AB->AC etc).

The Pivot table starts off defined as ''Data Tab'!$B:$AQ", as you note. Thus when a column is inserted within that span (e.g. at AA) the definition of the Pivot Table source is extended, as the final column ("AQ") is pushed along on to "AR".

This is why the definition of the Pivot table at the end of the macro runs out to AR.

Does this make sense?

So - at the end of the macro run, when the pivot table is to be refreshed, Excel is looking for a column header in the first row of each column from B to AR.

If only the columns B to AQ will have headers (and data) at the end of the Macro run, I'd suggest you need to change the initial definition of the Pivot Table to be ''Data Tab'!$B:$AP" so that, with the insertion of a single column, it's new definition at the end is ''Data Tab'!$B:$AQ"

Cal