Updating Pivot Table from Datasheet

[adam]

I changed it because having the line For rw = 5 To 53 also does the same thing as the line For rw = 28 To 53 meaning it does not hide the empty rows between the two pivot tables.

like I said before if you write the date range and hit the macro there would be empty unhidden rows between the two tables.

[HansV]

If I change the line to

For rw = 5 To 53

in the workbook that you attached, the blank rows between the first and second pivot table DO get hidden!

If you also want to hide blank rows between the second and third pivot table (you hadn't told us about the third one before!), increase the upper bound 53 to a suitable number, e.g. 59, or even 100 if you like.

[adam]

If you also want to hide blank rows between the second and third pivot table (you hadn't told us about the third one before!)

I had mentioned this part as following before this reply

Pivot table 02 is designed to show “statistics of total number of customers” including separate walk-in & walk-out customers who have received service within a given date range. This pivot table does not insert rows as it gets updated. Only the values in the corresponding cells get changed as the sheet “Summary” is updated.

Pivot table 03 is designed to show “statistics of sales for total number of customers” including separate walk-in & walk-out customers. This pivot table does not insert rows as it gets updated. Only the values in the corresponding cells get changed as the sheet “Database” is updated as this pivot table also gets updated with the information taken from the sheet “Database”.

Moreover I did try all the options you had mentioned like increasing the upper bound. Now like you said the rows DO get HIDDEN. but after the former date is written the total row of the pivot table 01 does not seem to show up???

[HansV]

I had mentioned this part as following before this reply

Just 45 minutes ago, but not earlier in this thread!

but after the former date is written the total row of the pivot table 01 does not seem to show up???

It does for me when I switch away from the sheet, then back, so that the Worksheet_Activate code runs...

[adam]

Thanks, Hans for letting me know what I have skipped. Now it works. & I'm happy

[adam]

because the first With... would be invalidated.

You can't just change things at random and expect them to keep on working correctly.

As you've mentioned above wouldn't it be possible in excel to filter date range as

As the date range is entered in the cell H1 & H2 Does excel has the ability to create a code that would filter the specified date range from the three pivot tables simultaneously.

Say for example I write 1/1/2010 in cell H1 & 31/1/2010 in cell H2 and press the macro button; the date range from the three pivot tables get filtered to show the data for the specified date range.

[HansV]

I have attached a modified version of your workbook. Please study it carefully, for I have changed a lot.

Sample Document with three Pivot Table.xlsm

- You had incorrect definitions for some of the named ranges. See Name Manager in the Formulas tab of the ribbon for the corrected definitions.

- You had mislabeled the pivot tables and some of their totals columns.

- The code has been changed to loop through the pivot tables instead of mentioning each separately.

I have assumed that the table in the Summary sheet will be updated by other means, as you told me in a PM.

[adam]

Thank you Hans for letting me know what I had done wrong & for helping me with what I was asking for.

In short, the sample workbook you have provided have given me all that I was asking for.

Once again Thanks for the help & I do really appreciate your work.