Consulting

Results 1 to 10 of 10

Thread: Help with RefreshAll

  1. #1
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    5
    Location

    Help with RefreshAll

    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!!
    Last edited by Aussiebear; 11-03-2023 at 04:42 AM. Reason: Added code tags to supplied code

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    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.
    Be as you wish to seem

  3. #3
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    5
    Location
    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!
    Last edited by Aussiebear; 11-03-2023 at 04:43 AM. Reason: Added code tags to supplied code

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    That code on its own could not possibly produce that error, since it is a procedure. What is the full code in the module?
    Be as you wish to seem

  5. #5
    Banned VBAX Newbie
    Joined
    Nov 2023
    Posts
    2
    Location
    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.


  6. #6
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    5
    Location
    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!

  7. #7
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    Turn off the background refresh option for the query.
    Be as you wish to seem

  8. #8
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    5
    Location
    Ideally, I would like the the whole file to update in the background without opening it - is that possible?

  9. #9
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    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.
    Be as you wish to seem

  10. #10
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    5
    Location
    I'll try to automate it thanks!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •