How to run the macro automatically 2

[beginner999]

How to run the macro automatically every time the Excel file is opened

Thanks n appreciate your help

 

[RivetHed]

Use the Workbook_Open Event.

 

[beginner999]

I put my code in the Workbook_Open Event. but it still not working ( like below). Am I doing something wrong. But when I run it manually it is working.

Private Sub Workbook_Open()
//code
End Sub

 

[AnotherHiggins]

You didn't put your code in a module, did you?

In goes in the workbook, not a module.

Press [Ctrl] + [R] to mke sure your project explorer is open - the thing that looks like Windows Explorer over on the left.

Double click on ThisWorkBook.

Now over on the right, where you put your code, change the top
left dropdown to Workbook.

Change the top right dropdown to Open.

Put your code in there.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[LindaEPI]

I am also trying to create a macro that will auto refresh each time the workbook is opened. I followed the instructions above. I'm not real knowledgeable about this. heres what I have.

Sub AutoRefresh()
'
' AutoRefresh Macro
' Macro recorded 4/29/2008 by WVCO
'
' Keyboard Shortcut: Ctrl+r
'
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub


What did I do wrong?

 

[AnotherHiggins]

Hi.

Please post new questions in new threads. It just helps to keep things tidy.

But I will go ahead and caution against using [Crtl]+[r] as a keyboard shortcut. It is already taken by Excel and is used to "fill right".

A good rule of thumb is to use [Ctrl]+[Shift]+ whatever key you want. So [Ctrl]+[Shift]+[r] would be a fine alternative and not cause any problems should someone actually use the [Crtl]+[r] shortcut as microsoft intended.

As to your issue, the fact that you have "Sub AutoRefresh" tells me that you have your code in a module. Re-read my previous reply about how to put your code in ThisWorkBook instead.

If you still have questions, please create a new thread.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[shVBA]

Also, once the code is in the open workbook module, yyou will need to tell VBA what it is refreshing ie:

Sheets("Sheet1").QueryTables(1).Refresh BackgroundQuery:=False

 

[PHV]

And why not simply set the RefreshOnFileOpen property of the QueryTable to True ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886