macro not working once filename changes

[XCESIV]

I am designing a master template. This will be set to read only. Once
this file is saved to another name the macros setup in the master file
no longer work. The macro i have a problem with is opening a data file
from the server and copying it to a spreadsheet. the macro can see the
data file but i am having troubles with it going back to the new file
as it is no longer called the master files name.

I hope this is all u need to help

Ty

 

[Guest]

Not too sure from your description but perhaps this will help. Thisworkbook
is the book that is running the code. So if your master workbook is the one
running the code then you can get back to it with

Thisworkbook.select

Otherwise as you create the spreadsheets and such use workbook objects to
keep track of the different books.

dim wbkMaster as workbook

set wbkMaster = workbooks("Master.xls")

With the above code even if the name of the master changes you can still
refer to it like this

wbkMaster.Select

 

[XCESIV]

The data is being picked up from a different workbook. to do this it
needs to open the 2nd workbook. In the master file it i have it
working, but not once i change the file name

Here is the line that is the problem

Windows("AFNE MASTER.xls").Activate

Once the file name is changed from this the macro can no longer go back
to this file. I need this line to recognise the change in file name

 

[Tom Ogilvy]

If you want to activate the workbook with the code

Thisworkbook.Activate

 

[XCESIV]

But when i go to the other workbook to gather the data that becomes the
active workbook and i want it to go back to the other workbook.

here is wat it is doing
* open report (where data i want to import is)
* select line 3 to line 100
* goto main workbook
* paste in line 5 of main workbook
* close report

Its getting back to the main workbook that i am having a problem with.
I know i could setup a linked cells to the report file, but this report
file is used by multiple ppl and can change often and want it via an
upload button.

 

[Tom Ogilvy]

Dim bk as Workbook
set bk = Workbooks.Open("C:\Somefolder\somfile.xls")

now you have a reffernce to the workbook

Thisworkbook.Activate

bk.activate

but if you know the names of the workbooks, you can also do

Assume book1.xls and book2.xls

Workbooks("Book1.xls").Activate
Workbooks("Book2.xls").Activate

of course you usually don't have to activate anything to work with it, but
you can look into that later.

 

[XCESIV]

thanks heaps for that. It worked. I just had to change 1 section
little

set bk = Workbooks.Open

to

Set bk = ThisWorkboo