I have a part of VBA code that reads:

Windows("Data 31 Dec 05.xls").Activate

This works fine at the moment but at somepoint the file is going to b
saved again with its name changed. This part of the formula kicks i
after 6 pages are copied out of the main document and into a new one
which is saved under a new file name, ie:

Macro is run
6 Pages are copied out into a new book
New book is saved
Old file (Data 31 Dec 05.xls) is selected.

What i want to be able to do is to have a formula in the main doc tha
reads:
=CELL("Filename") so that the correct filename is known, then have thi
copied in with the 6 pages and have the Windows formula link to thi
cell reference.

Any ideas

--
chalk
-----------------------------------------------------------------------
chalky's Profile: http://www.excelforum.com/member.php...fo&userid=2375
View this thread: http://www.excelforum.com/showthread.php?threadid=51326

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" wrote in
message ...

I have a part of VBA code that reads:

Windows("Data 31 Dec 05.xls").Activate

This works fine at the moment but at somepoint the file is going to be
saved again with its name changed. This part of the formula kicks in
after 6 pages are copied out of the main document and into a new one,
which is saved under a new file name, ie:

Macro is run
6 Pages are copied out into a new book
New book is saved
Old file (Data 31 Dec 05.xls) is selected.

What i want to be able to do is to have a formula in the main doc that
reads:
=CELL("Filename") so that the correct filename is known, then have this
copied in with the 6 pages and have the Windows formula link to this
cell reference.

Any ideas?


--
chalky
------------------------------------------------------------------------
chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=513265

How about using

Set oWB = Windows("Data 31 Dec 05.xls")
oWB.Activate
'6 Pages are copied out into a new book
'New book is saved
oWb.SaveAs new_filename
'Old file (Data 31 Dec 05.xls) is selected.
oWB.Activate


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" wrote in
message ...

I have a part of VBA code that reads:

Windows("Data 31 Dec 05.xls").Activate

This works fine at the moment but at somepoint the file is going to be
saved again with its name changed. This part of the formula kicks in
after 6 pages are copied out of the main document and into a new one,
which is saved under a new file name, ie:

Macro is run
6 Pages are copied out into a new book
New book is saved
Old file (Data 31 Dec 05.xls) is selected.

What i want to be able to do is to have a formula in the main doc that
reads:
=CELL("Filename") so that the correct filename is known, then have this
copied in with the 6 pages and have the Windows formula link to this
cell reference.

Any ideas?


--
chalky
------------------------------------------------------------------------
chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=513265

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

--
Regards,
Tom Ogilvy


"chalky" wrote in
message ...

I have a part of VBA code that reads:

Windows("Data 31 Dec 05.xls").Activate

This works fine at the moment but at somepoint the file is going to be
saved again with its name changed. This part of the formula kicks in
after 6 pages are copied out of the main document and into a new one,
which is saved under a new file name, ie:

Macro is run
6 Pages are copied out into a new book
New book is saved
Old file (Data 31 Dec 05.xls) is selected.

What i want to be able to do is to have a formula in the main doc that
reads:
=CELL("Filename") so that the correct filename is known, then have this
copied in with the 6 pages and have the Windows formula link to this
cell reference.

Any ideas?


--
chalky
------------------------------------------------------------------------
chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=513265

Thanks for the input. However the file will change name at any time -
It will remain as the normal filename for a quarter and then change, i
can change the code manually but would rather not as it will be handed
over to someone else non Excel minded


--
chalky
------------------------------------------------------------------------
chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=513265