Moving sheets with merged cells

[Andy Clarke]

Following the creation of a report on an Excel 2000 sheet, I wish to copy the required sheets only into a new book.

However, the cells may well have over 255 characters.

To get around this (using the cell Sheet1 as an example), the macro copies all worksheets within the workbook (renaming eg. Sheet1 (2)) Then it creates a formula linking back to the original Sheet1. Then the sheets can be moved into a new workbook, with a macro copyandpastevalues (to copy all cells and then paste values) to break the links.

However, the front sheet (Cover) is not automatically generated in the same way as the other sheets, and so may have merged cells across it. (in the region A13:DI34). When I copy within the workbook, these merged cells remain. However, the function .move when the new book is created does not carry the merging over.

It should be noted that when I record this macro manually using the macro recorder, it keeps the merged cells as required, but then when I replay the macro, it does not.

I have not put in a function to go back to the orignial sheet and copy formats and paste, as I do not want to name or specify what these sheets are called.

I am reluctant to go through on the new book and and put the merged cells on, as rows may be inserted, and there are rows 35 to 47 which do not want to be merged, and this will overcomplicate the already complicated macro.

I am moving on soon, so need this to be as idiot-proof as possibe (!), so if anyone can think of an easier way to get around the 255 character problem in a better way, it would be appreciated.

Sub Createreportfile()
Sheets(Array("Cover", "Sheet1")).Copy Before _:=Sheets(10)
Sheets("Cover (2)").Select
Range("A13:DI34").FormulaR1C1 = "=if('Cover'!RC=0,"""",'Cover'!RC)"
Sheets("Sheet1 (2)").Select
Range("A6", ActiveCell.SpecialCells(xlLastCell)).FormulaR1C1 = "=if('Sheet1 '!RC=0,"""",'Sheet1'!RC)"
Sheets(Array("Cover (2)", "Sheet1 (2)")).Select
Sheets("Sheet1 (2)").Activate
Sheets(Array("Cover (2)", "Sheet1 (2)")).Move
Sheets("Cover (2)").Select
CopyandPasteValuesAll
Sheets("Sheet1 (2)").Select
CopyandPasteValuesAll
Application.CutCopyMode = False
Sheets("Cover (2)").Name = "Cover"
Sheets("Sheet1 (2)").Name = "Sheet1"
Sheets("Cover").Select
End Sub