VBA Refresh pivot tables is adding extra blank rows to source table

[ExcelNoob222]

Hi.

I have some pivot tables built off of a table. As an example the table goes from A1-S300.

I have an intake tab where users can paste data into and then run a macro. The macro cleans up the data and then adds it to the bottom of the table. It then uses

ThisWorkbook.RefreshAll

To refresh all of the pivot tables.

However, after refreshing the pivot table, it adds 396 rows to my table and thus causing blank rows with #n/A and blank to show in my pivot tables.

The 396 does not match the amount of data i have added to the table and it is happening when the "ThisWorkbook.RefreshAll" is ran. If I remove this line of code and only update the table, the table has the correct amount of rows. If I manually refresh the pivot tables it also adds these blank rows.

Any ideas?

 

[Alex Blakenburg]

Are you using and actual Excel Table ?
If so what is the Table name ?
If you go to the data source of the pivot, what is showing there ?
If it is not a table name, is the code setting the datasource ?
Looking at the field with the #N/A, do you have lots of formula rows no data but #N/A in the field you are showing ?
If that is not the case, if you filter the table on that field is #N/A one of the options ? In which case filter on it and trouble shoot the #N/As

 

[ExcelNoob222]

Are you using and actual Excel Table ?
If so what is the Table name ?
If you go to the data source of the pivot, what is showing there ?
If it is not a table name, is the code setting the datasource ?
Looking at the field with the #N/A, do you have lots of formula rows no data but #N/A in the field you are showing ?
If that is not the case, if you filter the table on that field is #N/A one of the options ? In which case filter on it and trouble shoot the #N/As

Hi, the data source is a table. The data source does not reference the table name it just references $A$1:$AA$500. However, if I were to add one row to the table and refresh the pivot table it will automatically change the range to $A$1:$AA$501.

The macro that adds data to the table is working as intended. For example if I run that part and I add 10 rows the table is now $A$1:$AA$510. But as soon as I refresh the pivot table it is adding blank rows to the table and changing the range to $A$1:$A$750 as an example.

 

[Alex Blakenburg]

1) why aren't you using the table name as the source ?
2) if the data source is changing the code has to be doing it.
Are you triggering another macro with a worksheet change event ?
Have you perhaps done a find in the Project for the pivot name or DataSource:= ?
Can you share the full code or the workbook ?

 

[ExcelNoob222]

1) why aren't you using the table name as the source ?
2) if the data source is changing the code has to be doing it.
Are you triggering another macro with a worksheet change event ?
Have you perhaps done a find in the Project for the pivot name or DataSource:= ?
Can you share the full code or the workbook ?

Hi, sorry for such a late response. I appreciate trying to help me. I am unsure why the table name was not referenced. I ended up converting the table to a range (not sure why it was a table to begin with...) and now the update pivot macro resets the pivot range based on a lastrow formula. Thanks!

 

[Alex Blakenburg]

Thanks for the update, glad you were able to get it working.