1

I'm developing a userform that opens an excel file, imports a given number of rows and pastes the rows in a pivot table. Unfortunately, I have no idea how to refresh the pivot table in order to merge those new rows with the old ones.

If any of you guys could help me, it would be great, because I only have basic knowledge and would like to achieve this project.

Tim Hutchison
  • 3,483
  • 9
  • 40
  • 76
Clément Hurel
  • 87
  • 6
  • 1
    Check out https://stackoverflow.com/questions/70947/how-can-i-refresh-all-the-pivot-tables-in-my-excel-workbook-with-a-macro – Tim Hutchison May 24 '17 at 14:07

1 Answers1

0

You cannot paste (append) rows to a pivot table. You should add your raw data to the place that your pivot table refers to if you want them to be in your pivot table.

The important is the way that your pivot table is set up. You need to use ranges like "A:G" instead of "A1:G800" to make sure any row that will be added later will be included in your pivot table.

If you set up your pivot table like this, after you add your raw data if you refresh your spreadsheet the pivot table will be updated and newly added data will be included in it.

In the following link, I have some screenshots to how set up a pivot table for this purpose:

Count if for excel VBA, and printing results in another range

p.s. As mentioned by @Rory using an entire column for a pivot table is not efficient. It is recommended to use "named ranges" or "tables".

M--
  • 25,431
  • 8
  • 61
  • 93
  • Using entire columns for pivot table source data is inefficient and should be avoided. Use either dynamic named ranges or Tables instead. – Rory May 24 '17 at 13:54
  • @Rory What about step by step teaching? You won't graduate with a college degree from a primary school. I agree with you that it's not efficient but one lesson a day is my strategy. I included your point at the end of the answer. Thanks. – M-- May 24 '17 at 13:56
  • Apart from being inefficient, it also introduces the (blank) entry which you then have to filter out later. This can cause issues when you then add new items as unless you're careful, all previously unseen items will be filtered out also. Tables or Dynamic Ranges are definitely the preferred approach. – CLR May 24 '17 at 15:12
  • 1
    @Masoud That's why I just added it as a comment rather than downvoting. :) – Rory May 25 '17 at 07:03