When I am copying an entire sheet, with formulas, links, and merged cells,
to another workbook or new workbook, I use this code:

Dim Wb1 As Workbook 'Originating workbook
Dim Wb2 As Workbook ''Copy to" workbook
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\Your file to open.xls")

' Or

'Workbooks.Add
'Set Wb2 = ActiveWorkbook

Wb1.Sheets("Sheet1").Copy _
after:=Wb2.Sheets(Wb2.Sheets.Count)
Cells.Copy
Cells.PasteSpecial xlPasteValues

This retains all of the original formatting but sets all formulas and links
to values.


When I am copying portions of the sheet to another sheet:

Wb1. Activate
Range("So & So").Copy
Wb2.Activate
Range("So & So").PasteSpecial xlPasteValues
Range("So & So").PasteSpecial xlPasteFormats


Mr. Phillips is totally correct about Merged cells. They are a pain. They
require additional code and sometimes can not be set at all due to the
formatting of the worksheet being pasted to. Every case of Merged cells has
to be tested. If you feel you need merged cells, the best approach to Copy
is by using xlPasteValues & xlPasteFormats.

Regards,

Alan





"CAPTGNVR" wrote in message
ps.com...
DEAR ALL
I have about 16 sheets of various calculations. In some sheets i have
links from the remaining sheets which sort of sums up my reports for
the day to the company. I did face lots of problems like, i cant copy
and paste becos cells are not identical or merged cells problems etc.
At present i have been selecting cells-copy-workbook add- paste.
After some learning through this forum, i put offset formulas so that
i dont need to write it in VB. Now the problem is when i see the sheet
which i export t has #value messages.
I will be proceeding on leave from this ship-- so pls help me quickly
in advising me how to
1. Best way to copy from a sheet which has links, merged cells to a
new sheet.
2. Is there a way to code it in VB, where once i select the cells, I
would like to copy values, formats everything except the links and
formulas.
Eagerly awaiting n pls help.