Macro question - Need to copy data & formatting to another workboo

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 dont know whether it works as i just kind of lashed it together without testing it (probably because i dont really have a clue what i'm doing!), but i copied a sheet by selecting every cell and pasting the 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 the 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 Sub

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.

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.

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!

"[email protected]" 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.
>
>

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.

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.