I am trying to copy a worksheet from one Excel workbook to another workbook
using a macro, but all the formatting is lost using a copy/paste. How can I
do this since the path and workbook names are different? I cannot use the
copy worksheet feature by right clicking the tab because the destination
workbook is not open at the time of the copy. I have defined the path in the
macro and it works fine except for the formatting problem. Thanks.

This should select all cells on the sheet for which it is run, i don
know whether it works as i just kind of lashed it together withou
testing it (probably because i dont really have a clue what i'
doing!), but i copied a sheet by selecting every cell and pasting th
cells like below into a newly added sheet.

Hope it gets you some way if not all the way there!

P.S you might need to add a With statement to add the sheet in th
newly opened workbook.

Regards,
Simon

Sub ShtCopy()
Cells.Select
Selection.Copy
'''OPEN THE YOUR RECEIVING WORKBOOK HERE'''
'''SELECT YOUR NEWLY OPENED WORKBOOK'''
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
'''SELECT YOUR ORIGINAL WORKBOOK'''
'''CLOSE YOUR RECEIVING WORKBOOK'''
End Su

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=56556

Hello Rog,

Try this piece of code:
Sub copy_to_closed_workbook
Sheets("Sheet1").Select
Workbooks.Open Filename:="C:\Filename.xls"
Windows("Current Workbook.xls").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks("Filename.xls").Sheets(1)
End Sub

Let me know if it works.

Rog wrote:
I am trying to copy a worksheet from one Excel workbook to another workbook
using a macro, but all the formatting is lost using a copy/paste. How can I
do this since the path and workbook names are different? I cannot use the
copy worksheet feature by right clicking the tab because the destination
workbook is not open at the time of the copy. I have defined the path in the
macro and it works fine except for the formatting problem. Thanks.

It looks like it will work great! Thank you!

" wrote:

Hello Rog,

Try this piece of code:
Sub copy_to_closed_workbook
Sheets("Sheet1").Select
Workbooks.Open Filename:="C:\Filename.xls"
Windows("Current Workbook.xls").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks("Filename.xls").Sheets(1)
End Sub

Let me know if it works.

Rog wrote:
I am trying to copy a worksheet from one Excel workbook to another workbook
using a macro, but all the formatting is lost using a copy/paste. How can I
do this since the path and workbook names are different? I cannot use the
copy worksheet feature by right clicking the tab because the destination
workbook is not open at the time of the copy. I have defined the path in the
macro and it works fine except for the formatting problem. Thanks.