macro not working once filename changes
 

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


--
XCESIV
------------------------------------------------------------------------
XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271
View this thread: http://www.excelforum.com/showthread...hreadid=538258

macro not working once filename changes
 

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
--
HTH...

Jim Thomlinson


"XCESIV" wrote:


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


--
XCESIV
------------------------------------------------------------------------
XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271
View this thread: http://www.excelforum.com/showthread...hreadid=538258

macro not working once filename changes
 

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


--
XCESIV
------------------------------------------------------------------------
XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271
View this thread: http://www.excelforum.com/showthread...hreadid=538258

macro not working once filename changes
 

If you want to activate the workbook with the code

Thisworkbook.Activate

--
Regards,
Tom Ogilvy


"XCESIV" wrote in
message ...

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


--
XCESIV
------------------------------------------------------------------------
XCESIV's Profile:
http://www.excelforum.com/member.php...o&userid=24271
View this thread: http://www.excelforum.com/showthread...hreadid=538258

macro not working once filename changes
 

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.


--
XCESIV
------------------------------------------------------------------------
XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271
View this thread: http://www.excelforum.com/showthread...hreadid=538258

macro not working once filename changes
 

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.

--
Regards,
Tom Ogilvy



"XCESIV" wrote in
message ...

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.


--
XCESIV
------------------------------------------------------------------------
XCESIV's Profile:
http://www.excelforum.com/member.php...o&userid=24271
View this thread: http://www.excelforum.com/showthread...hreadid=538258

macro not working once filename changes
 

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

set bk = Workbooks.Open

to

Set bk = ThisWorkboo

--
XCESI
-----------------------------------------------------------------------
XCESIV's Profile: http://www.excelforum.com/member.php...fo&userid=2427
View this thread: http://www.excelforum.com/showthread.php?threadid=53825