VBA for copy/paste from file X to current file

[Liadxls]

Good morning,

Not being a 'knower' of VBA would it be possible someone could give me a code to replace the following indirect formulas?
If the code could be programmed to run on on Friday morning when the file is opened for the first time that would be great (in order to save ressources running every time the file opens).
The problem is the classic with indirect - it doesnt work with a closed workbook, hence the need for a code.

The code is to bring the results of last week into this weeks file to compare the progress.
The source workbook called w(x-1).xlsm is in the same network file (w(x).xlsm) as the destination file and changes every week from w1 to w2 to w3 etc, where the week number is driven by cell S17. This week being w2 and last week being w1 for example.

File w(x) Cell T17=INDIRECT("[w"&$S$17&".xlsm]Results!$T$101")
File w(x) Cell T18=INDIRECT("[w"&$S$17&".xlsm]Results!$T$100")
File w(x) Cell T19=INDIRECT("[w"&$S$17&".xlsm]Results!$T$98")
File w(x) Cell T20=INDIRECT("[w"&$S$17&".xlsm]Results!$T$99")
File w(x) Cell T22=INDIRECT("[w"&$S$17&".xlsm]Soot!$F$21")
File w(x) Cell T23=INDIRECT("[w"&$S$17&".xlsm]Soot!$G$23")

The result should be inserted as a value.

Thankyou very much if anyone could help.

[6StringJazzer]

If you want the results inserted as values then the VBA will have to open the file anyway to read it. Perhaps your VBA could update the formulas to be direct references:

Please Login or Register  to view this content.

and so forth

[Liadxls]

Good morning.

Would someone be able to suggest a code?

Thanks

[6StringJazzer]

I suggested code, for the first cell. You just need to copy that line of code and update for each cell.

[Liadxls]

No need to define variables or anything?

[6StringJazzer]

No, you are just converting a formula to a hard link, and the data you need is in other cells. No variables are needed.

[Liadxls]

Good morning.

Thanks for your help.

I inserted this code through VBA into the sheet where i need the result.
When I run the compiler I get an error highlighting the "=[w" as an invalid outside procedure.

I am doing something wrong?
Sorry but I never used this method so I have no clue how it should be inserted/run etc.


Thankyou

[6StringJazzer]

OK, I did not realize you had no background at all in VBA.

Open the VBA editor by hitting ALT-F11. See this page for details.

Right click on the tree under your file and select Insert Module. You will get a new module called Module1. Double click on it. Put the following code into that module. You did not say what worksheet you want these formulas to go it. This example uses Sheet1 but you will need to change it if it's not right.

Please Login or Register  to view this content.

Now you can run InsertFormulas from the Macros list.

[Liadxls]

Thanks.
Perfect.

Never know what to do at the start of the code 'public' etc.
Working fine.