Auto add formulas to new rows

[G1032]

Hi
I have a sheet that imports data from a website.
New data each day added to top row of sheet 1
Sheet 2 is used for calculations from the data on sheet 1.
I have sheet 2 formulas copied down to row 5000.
When I refresh the workbook and import new data each day sheet 2 gets auto populated with new data also. However the cells containing formulas at the bottom rows of sheet 2 don't auto update.

The question - How to I get the formulas to auto fill correctly down all rows of sheet do after new data is added to it? I have a macro set to do this but I'm looking for an automatic solution.

I thought putting the data in a table format would work but it didn't

 

[Joe4]

The question - How to I get the formulas to auto fill correctly down all rows of sheet do after new data is added to it? I have a macro set to do this but I'm looking for an automatic solution.

Call your Macro from an event, which will run you Macro automtically.
There are various different events. There are workbook-level events, like opening the workbook.
Then there are also sheet-level events, like the updating of cells (which would be triggered by a data import).
Or, if you have a macro to import the data, just call your other Macro at the end of that one.

Here is a good write-up on events: Events In Excel VBA

 

[G1032]

Call your Macro from an event, which will run you Macro automtically.
There are various different events. There are workbook-level events, like opening the workbook.
Then there are also sheet-level events, like the updating of cells (which would be triggered by a data import).
Or, if you have a macro to import the data, just call your other Macro at the end of that one.

Here is a good write-up on events: Events In Excel VBA

Thanks! But I have no clue what I'm at.
The data is imported from a url I inputted in 'Get Data' > 'From Web'.
The macro is as simple as this:

Application.ScreenUpdating = False
Range("A2:Q10000").Select
Selection.FillDown
Range("A1").Select

Sheet 2 is the sheet I need to update, only after the data has been imported.
The workbook is set to automatically refresh upon opening.

I put that macro code as per second screenshot below and after the data imports there is a bit of a hang and then the view code pops up as in 2nd screenshot.

How to I call the macro once the data has auto imported?
Thanks

 

[Joe4]

Upon opening the file, about how long does it take to refresh the data?

 

[G1032]

Upon opening the file, about how long does it take to refresh the data

Not long. About 5 seconds tops I'd imagine. I'm on mobile now but can check for exact time later.

 

[Joe4]

OK, then one way would to be use a "Workbook_Open" event procedure, which runs when a workbook is first opened, and have it wait something like 10 second before calling your macro.
Here is a link on Workbook_Open: Excel Workbook.Open Event Access-Excel.tips
and here is a link on the timer: Wait, Delay, Create a Timer While Running Code - VBA Code Examples

So your final code would look something like:

Private Sub Workbook_Open()
    'Wait 10 seconds
    Application.Wait Now + TimeValue("00:00:10")
    'Call your macro
    Call MacroName
End Sub

If you want to tell them to wait while it is running, you can do something like this:

Private Sub Workbook_Open()

    MsgBox "Please wait while macro runs", vbOKOnly
   
    'Wait 10 seconds
    Application.Wait Now + TimeValue("00:00:10")
   
    'Call your macro
    Call MacroName

    MsgBox "Macro complete!", vbOKOnly
   
End Sub

 

[G1032]

That's fantastic. Thanks so much for that. I will try this when I get home later. Gent.

 

[G1032]

OK, then one way would to be use a "Workbook_Open" event procedure, which runs when a workbook is first opened, and have it wait something like 10 second before calling your macro.
Here is a link on Workbook_Open: Excel Workbook.Open Event Access-Excel.tips
and here is a link on the timer: Wait, Delay, Create a Timer While Running Code - VBA Code Examples

So your final code would look something like:

Private Sub Workbook_Open()
    'Wait 10 seconds
    Application.Wait Now + TimeValue("00:00:10")
    'Call your macro
    Call MacroName
End Sub

If you want to tell them to wait while it is running, you can do something like this:

Private Sub Workbook_Open()

    MsgBox "Please wait while macro runs", vbOKOnly
  
    'Wait 10 seconds
    Application.Wait Now + TimeValue("00:00:10")
  
    'Call your macro
    Call MacroName

    MsgBox "Macro complete!", vbOKOnly
  
End Sub

Hmmmm
So that didn't work. I got no error or crash but got no macro to run either. Then I thought it was because the sheet the macro needs to run on wasn't activated or selected so added Worksheets("Averaged").Activate before the macro call but still nothing. Then tried Sheets("Averaged").Select and no macro call.
The sheet is auto saving though after it refreshes and before the 'wait' time is over. Would that affect the macro call?

 

[Joe4]

What is the name of the VBA module where you have placed my code?
You didn't put it in the same folder as you originally had the other code, did you?
Note that in order for the code to run automatically, you MUST put my code in the pre-existing "ThisWorkbook" module.
If you put it anywhere else, it won't work.

 

[G1032]

What is the name of the VBA module where you have placed my code?
You didn't put it in the same folder as you originally had the other code, did you?
Note that in order for the code to run automatically, you MUST put my code in the pre-existing "ThisWorkbook" module.
If you put it anywhere else, it won't work.

BOOM! Thanks. That works now. You're a gent.
One last question on this if you have time? Is it possible to call that macro after background query has run as opposed to after a certain a time delay? I only ask because in the event of a slow internet connection or slow processing power the macro will run before the query has run?