How to copy workbook?

[LF]

Hello,

I have a workbook open and I want to populate it with the content of another
xls file. How can I do that. I wan everything transferred: values, formulas,
embedded object, formatting, everyhting. I figured that I coould do a sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a SaveCopyAs
on the source workbook and then open that. I need to work with the one that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

 

[Max]

Why not just do a File > Save As, within the existing .xls that you want
everything retained ?
(give it a new file name)

 

[macropod]

Hi LF,

To make a complete replica, you'd be better off simply copying the file with
Windows Explorer. Otherwise, if your source workook has multiple sheets with
formulae referring to different sheets, you're likely to end up with the
links to the original source workbook, not the copy.

Having said that, if you really want to copy & paste everything, use Ctrl-A
then Ctrl-C to copy the sourceworksheet, then Ctrl-V to paste it into the
target.

Cheers

 

[LF]

Max,
I already have the workbook opened where I want to bring things in (from a
file). I cannot use SaveAs.
Regards,
Levente

 

[LF]

Hello,

I am not sure I understand. When you copy over from one worksheet to
another, don't yopu simply copy the formulas? If I will co this copying for
all the sheets in the source workbook, shouldn't I recreate the exact
strucutre of the original workbook?

Also, is the Worksheet.Copy method working for copying from one workbook to
another? This supposedly copies everything.

Regards,
Levente

Hi LF,

To make a complete replica, you'd be better off simply copying the file
with
Windows Explorer. Otherwise, if your source workook has multiple sheets
with
formulae referring to different sheets, you're likely to end up with the
links to the original source workbook, not the copy.

Having said that, if you really want to copy & paste everything, use
Ctrl-A
then Ctrl-C to copy the sourceworksheet, then Ctrl-V to paste it into the
target.

Cheers

--
macropod
[MVP - Microsoft Word]

Hello,

I have a workbook open and I want to populate it with the content of another
xls file. How can I do that. I wan everything transferred: values, formulas,
embedded object, formatting, everyhting. I figured that I coould do a sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets
in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs
on the source workbook and then open that. I need to work with the one that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

 

[Max]

If you do File > Save As (for the "complicated" source .xls), you could then
build it up further from there. I'm presuming of course, that the "other"
workbook you have is almost like a "new" workbook which contains only some
work easily copied & pasted over into this "saved as" copy, in new sheets
for example. Anyway, this is the route I'd naturally take for such
instances.

 

[Max]

If you do File > Save As (for the "complicated" source .xls), you could then
build it up further from there. I'm presuming of course, that the "other"
workbook you have is almost like a "new" workbook which contains only some
work easily copied & pasted over into this "saved as" copy, in new sheets
for example. Anyway, this is the route I'd naturally take for such
instances.

 

[NickHK]

You say that you want "to make the opened document identic to the one I am
copying from".
If so then, create a copy of the file first, then open the copy:

name oldfile as newfile
workbooks.open newfile

However, if you really mean that you want to import all the sheets from the
oldfile into the newfile :
Dim SourceWB as workbook
Dim DestinationWB as workbook
dim WS as worksheet

set SourceWb =workbooks.open(oldfile)
set DestinationWb =workbooks.open(newfile)

for each ws in sourcewb.worksheets
WS.Copy After:=DestinationWb.Sheets(DestinationWb.sheets.count)
next

NickHK

 

[LF]

Thanks Nick,

And this also answers my question if I can use the Workshhet.Copy method to
copy from one workbook to another. I will try this later today. Assuming I
do these steps:

1. Delete all chart sheets from the opened destination file
2. Delete all sheets from the destination file (except one, as it cannot be
left empty, exception thrown on deleting the last one).
3. Copy all sheets and then all chart sheets from the source file
4. Delete the one leftover sheet from the original content of the
destination

I guess that this should get me into the position where the content of the
destination is exactly like to content of the source. I mean values,
formulas, formatting (cell types, fonts, grouping, borders, etc.), embedded
charts and other COM objects (like embedded Word documents) in sheets are
all transferred. I really hope that this will work. In case there are linked
OLE objects, I assume that this would make the content of the destination
refer to the same sources of the links as in the source workbook. Is this
all correct?

Regards,
Levente

 

[macropod]

Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not Sheet2 in
the copy.

Cheers

 

[LF]

Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets? Can I
adjust the formulas after copying. Any solution?

 

[LF]

Can I copy several sheets at once, from one workbook to another, using
Worksheets.Copy? In this case, will the references to the formulas that are
in the copied group of sheets maintained correctly?

 

[NickHK]

But as your other replies have indicated, there is no reason to do it this
way and you will make your like more difficult, re formulae pointing to the
wrong workbook.
Make a copy of the WB first, then open it. Either Excel.SaveAs or the VBA
"Name OldFile As NewFile".

NickHK

 

[macropod]

Hi LF,

The way around this would be to create all the required worksheets in the
new workbook, with the same names as in the source workbook, then CUT (not
copy) & paste the source workbook's contents into the new workbook, after
which you close the source workbook without saving the changes.

Cheers

 

[Saruman]

You must have one sheet left in the Workbook when you do this!! You may need
to insert a blank sheet to start with.

Why not open both files, group all the sheets together by holding down the
Shift key and clicking on the last sheet, which will highlight every sheet
from the start to where you are clicking.

Then right click on the grouped sheets and select the Move or Copy option.

In the dropdown box, select the file name where you want the pages to go to.

Select before which current sheet where you want the sheets to appear. Click
OK.

Done.

Try it on a copy first just in case of problems.

Saruman

 

[LF]

Saruman,

I am only interested in doing this programatically.

Regards,
Levente

 

[LF]

Nick,

Please do not start a debate about whether is a reason to do it like I am
planning to do it. The workbook is ALREADY open at the time I am starting
this operation and I CANNOT CLOSE IT. Still, I need the good content in my
workbook (that is stored elsewhere at the time).

Best regards,
Levente

 

[LF]

Hello all,

I managed to copy all worksheets and chart sheets. However, the formulas are
referring to the source file (as I was warned). I would like to iterate all
cells and update the formulas for each (seems simple, I only need to change
the text of the formulas and replace "[old_source_file.xls]" with ""). Can
this be done?

Also, can I modify each embedded chart object on each worksheet and change
it to not refer to the old workbook file (in case the chart was using data
from a different sheet). Where is the chart's data/formulas kept? Can I
accees the formulas?

Best regards,
Levente

Hi LF,

The way around this would be to create all the required worksheets in the
new workbook, with the same names as in the source workbook, then CUT (not
copy) & paste the source workbook's contents into the new workbook, after
which you close the source workbook without saving the changes.

Cheers

--
macropod
[MVP - Microsoft Word]

Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets? Can
I
adjust the formulas after copying. Any solution?

 

[macropod]

Hi Levente,

A simple Find/Replace operation on each worksheet will suffice, as proposed.
Do note that, if your new workbook's sheets aren't named the same as in the
old one, the cross-sheet links will fail.

Cheers

--
macropod
[MVP - Microsoft Word]

Hello all,

I managed to copy all worksheets and chart sheets. However, the formulas are
referring to the source file (as I was warned). I would like to iterate all
cells and update the formulas for each (seems simple, I only need to change
the text of the formulas and replace "[old_source_file.xls]" with ""). Can
this be done?

Also, can I modify each embedded chart object on each worksheet and change
it to not refer to the old workbook file (in case the chart was using data
from a different sheet). Where is the chart's data/formulas kept? Can I
accees the formulas?

Best regards,
Levente

Hi LF,

The way around this would be to create all the required worksheets in the
new workbook, with the same names as in the source workbook, then CUT (not
copy) & paste the source workbook's contents into the new workbook, after
which you close the source workbook without saving the changes.

Cheers

--
macropod
[MVP - Microsoft Word]

Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets? Can
I
adjust the formulas after copying. Any solution?

Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not
Sheet2
in
the copy.

Cheers