copy sheet from one workbook to another including formats and values

[Kaps_mr2]

Hi,

Its about 5 years since I did any VBA. How do I copy the contents of one worksheet into another including formats and values ? Here is what I have so far. Thanks

Kaps

Sub copy_sheet()
ActiveSheet.CopyWith ActiveWorkbook     .SaveAs Filename:=Environ("TEMP") & "\New1.xls", FileFormat:=5153     .Close SaveChanges:=FalseEnd With
End Sub

 

[KennyGreens]

Hi, the code you posted just copies the active sheet into a new, separate workbook, then saves it. Are you looking to copy the sheet to a specific workbook?

 

[Kaps_mr2]

hi,

no to a brad new workbook. thanks

Kaps

 

[KennyGreens]

I'm not incredibly familiar with the fileformat numbers, however assuming it's a valid number I don't see anything inherently wrong. What is the issue with what you posted?

 

[Kaps_mr2]

it won't post the values - it posts the formulae.

 

[Kaps_mr2]

now i have:-

Option Explicit
Public new_book As Workbook
Public old_Book As Workbook
Sub copy_sheet()




Set old_Book = ActiveWorkbook
Sheets.Copy


Workbooks.Add
Set new_book = ActiveWorkbook


new_book.Sheets(1).Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats


End Sub