File name change macro problem

[Potsy]

I have a macro which when you press a button opens another excel file
to cross-check and keep both open as two are linked via VLOOKUP,
however, the "Estimate MASTER v2.4" file name changes by user and save
as to the relevant estimate number and it then macro seems to throw a
wobbly due to file change - is there any way to just tell it to look
for open document not the file name "Estimate MASTER v2.4"???

"P:\01 Estimates\01 Estimate Record Book\Estimate Book.xls"
Windows("Estimate MASTER v2.4.xls ").Activate
End Sub

Sorry not very good in VBA...learning!!!

 

[JE McGimpsey]

Take a look at the GetOpenFileName() method in XL/VBA Help.

 

[Dave Peterson]

If "estimate master v2.4.xls" is the workbook that contains the code, you could
use:

Thisworkbook.activate

If it's the workbook that you opened, you could use a variable to represent that
workbook:

Dim OtherWorkbook as workbook
....
Set OtherWorkbook = workbooks.open(filename:=....)
....
Otherworkbook.activate

You could do the same thing if the workbook was active when you started.

Dim ActWkbk as workbook
set ActWkbk = activeworkbook
'do a bunch of stuff
Actwkbk.activate

I like using the workbook collection to activate a workbook. It causes less
trouble than the windows collection (for me). Especially when there could be
multiple windows into that workbook open--and even hidden.

 

[Potsy]

If "estimate master v2.4.xls" is the workbook that contains the code, you could
use:

Thisworkbook.activate

If it's the workbook that you opened, you could use a variable to represent that
workbook:

Dim OtherWorkbook as workbook
...
Set OtherWorkbook = workbooks.open(filename:=....)
...
Otherworkbook.activate

You could do the same thing if the workbook was active when you started.

Dim ActWkbk as workbook
set ActWkbk = activeworkbook
'do a bunch of stuff
Actwkbk.activate

I like using the workbook collection to activate a workbook. It causes less
trouble than the windows collection (for me). Especially when there could be
multiple windows into that workbook open--and even hidden.

thanks Dave!!! legend