Force Macro to Select Correct activeworkbook?

[Demo8II]

Hi!

I need to make a correction to 25 budget files I sent out to our field employees. So I want to send a new file, with a macro button in it, that will run a macro I have written to fix the error in these 25 budget files.

There are 25 field employees, each with their own version of this file, and they may have changed the names on the budget file I sent them.

My question is how can I make the macro i am sending out make the budget file (which will already be open on their computers) the active file, so that my macro fixes the correct file?

So What I'm wondering is if there is a way to make them select the budget file to make it the activeworkbook, as part of the macro i am sending them. This way, I won't need the line Windows("BudgetFile1.xls").Activate because they may have changed the name of the file.

Here is the sub I want to run: (I simplified it for the example)




Sub FixBudgetFiles()


Windows("BudgetFile1.xls").Activate

Sheets("Rollup").Activate
Range("AT737").Select
ActiveCell.FormulaR1C1 = "=R[235]C"


ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub


Any ideas or help would be very appreciated.

Thanks,
Demo8II

 

[Norie]

Why don't you use the reference to the workbook, w, that you've created?

Maybe something like this.

Sub FixBudgetFiles()
Dim w As Workbook
    Set w = ActiveWorkbook
    w.Sheets("Rollup").Range("AT737").FormulaR1C1 = "=R[235]C"
    w.Save
    w.Close
End Sub

 

[Demo8II]

Whoops, that Dim w as workbook was supposed to be deleted from that example.

Or -- I need the w to refer to the budget file, which will not be the file I send to them with the macro in it.

Does that make sense?

 

[Norie]

I'm a little confused - what workbook do you want to refer to?

If it's the workbook with the code in it then you could use ThisWorbook rather than ActiveWorkbook.

That should refer to the workbook the code is in rather than the one that VBA considers the active workbook.

 

[Demo8II]

First thanks for your help...I really appreciate it.

The macro will be running in the file I send out to the field employees, but needs to act on the budget file they have on their computer.

because field employees tend to be excel ignorant, I want my macro to automatically select the budget file to act on. The problem is that the budget file could be named anything.

So i'm wondering if there is a macro that will let the user select the file to do the work on somehow, like an inputbox that lets them select the budget file, which will then make it the activeworkbook.

Thanks again...hope i'm making sense here...