Linked workbooks

[AndyW]

Hi,

I have a report template that I have just developed which
is populated from 5 other spreadsheets that are emailed
to me. I save these to a folder and the report template
is linked to them so that when I open it it is update via
the links. I then use SaveAs to save the template as a
XLS report with the date appended. I then email the
report out to intended recipients.

The question I have is;
I no longer need the links in the sved report once the
report is completed and would like to delete them prior
to emailing. I obviously need to maintain the links in
the template (held in a different folder) for next months
report. The emailed report just need to maintain the
figures and not the links.

Any ideas or thoughts appreciated.

Andy (Perth WA)

 

[RagDyer]

This will not only remove your links, but also *every* formula in the sheet!
It will leave only the data behind.
*No* further calculations can be made.

With the report open, do:
<Ctrl> <A> (selects the entire WS)
Right click in the selection and choose "Copy".
Right click again, and choose "Paste Special".
Click on "Values", then <OK>, then <Esc>.

Your report is now in the configuration, as if you keyed in every data
entry.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,

I have a report template that I have just developed which
is populated from 5 other spreadsheets that are emailed
to me. I save these to a folder and the report template
is linked to them so that when I open it it is update via
the links. I then use SaveAs to save the template as a
XLS report with the date appended. I then email the
report out to intended recipients.

The question I have is;
I no longer need the links in the sved report once the
report is completed and would like to delete them prior
to emailing. I obviously need to maintain the links in
the template (held in a different folder) for next months
report. The emailed report just need to maintain the
figures and not the links.

Any ideas or thoughts appreciated.

Andy (Perth WA)

 

[Gord Dibben]

Andy

Select all cells(CRTL + A) and Paste Special>Values>OK>Esc before saving As.

Gord Dibben Excel MVP

 

[Guest]

Thanks I had tried this and you are correct it does
eactly as you say, however I has to paste twice one for
data and one for format. What I forgot to mention was
that I have 10 charts on the next sheet which is
populated from the same source data.

I've found that selecting them all and then paste special
as a Metafile is the only way I can copy them without
maintaining the link.

I was going to ask if I can automate this but I think
I've just answered my own question. Because the template
is always used to create this report I could probably set
up a macro to do the copy and pasting into a new workbook.

Is there any way I can automate the naming of the new
workbook so that the operation of copying, opening a new
workbook, pasting the data and then saving as <File Name>
could be all one button press? The name would be
something lik QIS Report JUN04.XLS

thanks for the assitance

Andy W (Perth - Western Australia)