QUESTION: I am running a massive workbook where each day of the week is represented by a separate "worksheet". Each worksheet has an external data source on an intranet. I have that data feeding a pivot table, then I paste link that pivot table data to an "Overview" worksheet.
Each day's worksheet has VBA code to update the pivot table when the data in column 1 is changed:
Private Sub Worksheet_Change(ByVal Target As Range)
' Update the pivot table on this worksheet if data in columns D or E is changed
If (Target.Column = 1) Then
The problem is that no matter what day of the week we are on, the workbook updates ALL of the pages, not just the active day. This cause the workbook to pause and drains computer resources for about 20 seconds while the update occurs at the intervals I set.
The overview worksheet is full of conditional formatting to render the non active days data blank and only show the active day's data relating to a reference cell with =Today()
If you can help, I would like to ONLY refresh the current day's worksheet, and that in turn will populate the Overview worksheet with the current results. Effectively reducing the heavy load.
I would prefer a VBA solution, but any solution is better than what I have now.
Thank you in advance
ANSWER: Hi Robert,
How are your worksheets with the pivot tables named exactly?
---------- FOLLOW-UP ----------
QUESTION: Each worksheet is named for a day of the week: Sat, Sun, Mon... Each worksheet has an intranet query set to refresh every 15 minutes, it the query has new data, the range will expand. The VBA on each page looks at those columns and if it sees a change, it refreshes the pivot table on that page. Excel then recalculates the values and updates the "pasteLink" to the "Overview" worksheet.
The coalescing worksheet is named: "overview"
Each worksheet has the VBA to refresh the pivot table if the data in Column D or E changes.
The Non active days would not have changes to column D or E, so my original request was misplaced as the problem is not the individual worksheets.
The real problem is the "overview" page that has VBA code that refreshes each worksheet and each pivot table then recalculates the results regardless of the day or date.
Sorry about the confusion, I really am trying to learn this stuff.
So, the problem is how to get ONLY the current day worksheet to be singled out for the update.
dim ws as worksheet, pt as pivottable
for each ws in thisworkbook.worksheets
for each pt in w.pivottables
When this executes, every worksheet ( Sat - Fri) updates, causing a recalculation on the overview page.
How do I put an exclusion to only look at the current day's worksheet?
I have included a hidden column with cell A1 to have =Today() for reference on each page. The code could reference that cell for exclusion purposes.
While I was waiting for your answer, I did some digging and included and if, then, else for each day, but I think is stepping through those and doing Sat, then the next refresh interval doing Sun, then the next refresh interval doing Mon... and so on...
I have taught myself excel and perform the research for projects I undertake, but this is really making me nuts.
I hope I explained it correctly this time.
Not sure I understand, but in your first message you showed the change event, which seems to do the single worksheet pivot table refresh already? That would make refreshing the pivot tables from the overview worksheet unneeded.
However, if you are looping through the sheets and only want to update the sheet with today's name, you can:
For each ws in Worksheets
If lcase(ws.name)=lcase(format(Date,"ddd")) Then
'This is the one for today!