Excel/Excel VBA

Advertisement


Question
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
ActiveSheet.PivotTables(1).RefreshTable
End If
End Sub

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
   p.refreshtable
   p.update
 next
next
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.

Answer
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!
End If
Next
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.