PDA

View Full Version : [SOLVED:] Help with RefreshAll


De92b
11-03-2023, 02:55 AM
Hello,

I'm new to VBA and am trying to set up a automatic RefreshAll upon changes in my worksheets.
My worksheets are linked to another workbook (A) and I would like to automatically refresh workbook B (which contains a query and pivot table) when there is data entered into workbook A. So far when data is entered into workbook A, the data is pulled into workbook B, however the query and pivot table don't update because my VBA code doesn't seem to work.

I entered the following in to the two worksheets in workbook B:


Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub


Do I need to enter any further code to enable the VBA or change any settings? (it is a macro-enabled workbook and VBA macro is allowed to run).

Thanks so much in advance!!
Aflatoon
11-03-2023, 03:03 AM
How is the data pulled into workbook B? If it's by formulas, then the Change event will not be triggered. You'd need the Calculate event.
De92b
11-03-2023, 04:12 AM
Yes, it is formulas.
When I change the code to:



Private Sub Worksheet_Calculate()
ThisWorkbook.RefreshAll
End Sub


I get a compile error 'invalid outside procedure'. So that doesn't seem to work either :(

Thanks!
Aflatoon
11-03-2023, 05:01 AM
That code on its own could not possibly produce that error, since it is a procedure. What is the full code in the module?
Miaksol
11-06-2023, 07:02 AM
In workbook B, how is the data pulled in? The Change event won't be triggered if it's through formulae. The Calculate event would be required.
De92b
11-06-2023, 08:46 AM
Seems to work now :) thanks!!
Buuut one more question: The pivot table auto refreshes at the same time the query refreshes, so in the end the pivot table doesn't contain the new data. How can I trigger the pivot table to refresh again after the RefreshAll? Thanks a lot!
Aflatoon
11-06-2023, 09:12 AM
Turn off the background refresh option for the query.
De92b
11-08-2023, 03:25 AM
Ideally, I would like the the whole file to update in the background without opening it - is that possible?
Aflatoon
11-08-2023, 04:29 AM
No, you can't refresh queries or pivot tables without opening them, but you can automate that if necessary, or simply have the refresh happen as soon as you open the workbook.
De92b
11-08-2023, 06:31 AM
I'll try to automate it :) thanks!