Select All and copy and paste

[Ashok]

Hi,
I am writing a macro / Com Addin, in which one the
requirement is to duplicate a existing / a open Excel
Workbook. The only thing that i found was the worksheet
copy / move method. Also, in this method the data does not
go to the clipboard, So if i copy something and close
excel and reopen, i will not have anything the clipboard.
Moreover, If I need to copy the whole work book, I need to
copy the sheets one by one. Is there a simpler way to do
this?
(something similar to select all and copy and paste. If we
do this in word, even the header footer and the styles get
copied). For some reasons, I dont want save the
Workbook and copy the file to duplicate the
workbook.

Thanks in advance

Ashok

 

[Ron de Bruin]

Maybe you mean this

requirement is to duplicate a existing / a open Excel Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"

 

[Guest]

Not exactly, I dont want to save the workbook and copy it
as a file nor saveas or save a copy as. I want something
equivalent to select all which should select all the
elements of a workbook, like all the worksheets and the
chart and pivot tables, the data, formula, etc. and copy
it and probably paste it in a new workbook or a existing
open workbook.

Thanks
Ashok

-----Original Message-----
Maybe you mean this
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Ashok" <[email protected]> wrote in

message news:[email protected]...

 

[Tom Ogilvy]

With Workbooks("Destination.xls")
Activeworkbook.sheets.copy After:=.Sheets(.Sheets.count)
End With

 

[Ashok]

Thanks for the code snipet. This was the approach that i
had intially used. But the problem with this is, the
source workbook has to be open for the copy to work and so
does the Excel application. If as a com addin, if i give
this functionalty, we cant expect the user to keep the
source workbook open or the user might click on copy and
close the excel itself and reopen excel and paste. Is
there a way to select all the elements and put in a
clipboard or something and paste it from the clipboard,
which would be ideal and would solve both the problems
that i have.

Thanks
Ashok

 

[Tom Ogilvy]

No, you can't use the vba copy method unless both workbooks are open in
Excel.

You should be able to accomplish that with your code, including guarding
against problems such as the workbook not being accessible.

 

[Dave Peterson]

If the workbook is not open, couldn't you just use FileCopy (see VBA help for
detail) to copy it to a new file/location?

 

[Tom Ogilvy]

Not exactly, I dont want to save the workbook and copy it
as a file nor saveas or save a copy as.

He already rejected that.