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

Maybe you mean this

requirement is to duplicate a existing / a open Excel Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in message ...
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

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

requirement is to duplicate a existing / a open Excel
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in
message ...
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



.

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

--
Regards,
Tom Ogilvy

wrote in message
...
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

requirement is to duplicate a existing / a open Excel
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in
message ...
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



.

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
-----Original Message-----
With Workbooks("Destination.xls")
Activeworkbook.sheets.copy After:=.Sheets(.Sheets.count)
End With

--
Regards,
Tom Ogilvy

wrote in message
...
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

requirement is to duplicate a existing / a open Excel
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in
message ...
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



.



.

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.

--
Regards,
Tom Ogilvy

"Ashok" wrote in message
...
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
-----Original Message-----
With Workbooks("Destination.xls")
Activeworkbook.sheets.copy After:=.Sheets(.Sheets.count)
End With

--
Regards,
Tom Ogilvy

wrote in message
...
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

requirement is to duplicate a existing / a open Excel
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in
message ...
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



.



.

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?

Ashok wrote:

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
-----Original Message-----
With Workbooks("Destination.xls")
Activeworkbook.sheets.copy After:=.Sheets(.Sheets.count)
End With

--
Regards,
Tom Ogilvy

wrote in message
...
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

requirement is to duplicate a existing / a open Excel
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in
message ...
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



.



.


--

Dave Peterson

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.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
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?

Ashok wrote:

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
-----Original Message-----
With Workbooks("Destination.xls")
Activeworkbook.sheets.copy After:=.Sheets(.Sheets.count)
End With

--
Regards,
Tom Ogilvy

wrote in message
...
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

requirement is to duplicate a existing / a open Excel
Workbook

ActiveWorkbook.SaveCopyAs "C:\something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ashok" wrote in
message ...
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



.



.


--

Dave Peterson