Updating Pivot Tables Dynamically

I'm currently looking to implement a solution to automate the process of
updating 5 pivot tables (one after another) on the same worksheet in an Excel
workbook.

Within the workbook there is a worksheet which is used as the data source
for the pivot tables.

If the number of items in the data source does not change the pivot table
updates without any problems. However if the number of items in the data
source increases the pivot tables start overlapping which causes errors. My
guess here is that you can’t place pivot tables in a worksheet and have them
adjust their size dynamically.

One solution I've have tried to move the pivot table into separate work
sheets in the workbook. Then using C# and the Excel library, open the
workbook, update the pivot tables and then move them into a single page.
However this approach does not work because an error occurs when moving the
second pivot table and them overlapping. In addition using C# and the Excel
library is very error prone because the errors aren’t very descriptive and
Excel does not always close correctly.

Do you know of another way how we can resolve this issue? Ideally the Excel
workbook should not contain any macros, hence the reason why C# was used to
generate the spreadsheet.

You could space your pivot tables out evenly across the whole worksheet ie columns A, AZ,CY, EX & GW and make the width of the columns between them very small eg 0.1 (not zero) so that the tables appear next to each other. You will need to hide gridlines for the sheet to prevent the narrow columns appearing as a grey band. The pivot tables will automatically widen the columns if there is more data.

Huron - Thanks for your response and it's an excellent suggestion.

However I should have said the pivot tables are laid out one below another, and not side by side.

Taking your suggestion I’ve also thought about hiding and resizing the rows between two pivot tables but unfortunately refreshing doesn’t automatically show/adjust their row height when refreshed.

In addition the conditional formatting gets lost in the process.