You could add an additional line

Range("your_range").PasteSpecial (xlPasteColumnWidths)

which would sort out the cell width, but I'm not aware there is an
equivalent for row heights. So you'd need to capture this in a
variable

So for instance for a single cell named "Your_Range1" that you're
copying to a cell named "Your_Range2" somewhere else in the workbook



Sub CopyPasteFormats()
Dim iCellHeight As Integer
iCellHeight = Range("YourRange1").RowHeight
Range("Your_Range1").Copy
Range("Your_Range2").PasteSpecial (xlPasteAll)
Range("Your_Range2").PasteSpecial (xlPasteColumnWidths)
Range("Your_Range2").RowHeight = iCellHeight

End Sub

If you've got several ranges to copy and paste you'd be advised to
create a looping procedure which would pass the range names to the
above procedure. So for instance - untested

Sub CopyFromTo
'Calling macro - only shows one set of ranges for simplicity. Put in a
'loop to selectively call other pairs of ranges

Dim rFrom as Range, rTo as Range

rFrom=Range("Your_Range1")
rTo=Range("Your_Range2")

Call CopyPasteFormats(rFrom,rTo)

End Sub


Sub CopyPasteFormats(rFrom as Range,rTo as Range)
Dim iCellHeight As Integer,
iCellHeight = rFrom.RowHeight
rFrom.Copy
rTo.PasteSpecial (xlPasteAll)
rTo.PasteSpecial (xlPasteColumnWidths)
rTo.RowHeight = iCellHeight

End Sub

HTH


On Sun, 7 May 2006 12:00:36 -0700, "Yo****ha"
wrote:

Hi Richard

I've used the following one
Range("your_range").PasteSpecial (xlPasteAll)
to paste into new sheet, but in new sheet it is not maintaining the width
and height for the cell, i.e in the original sheet if it is having more
width and in the copied sheet it is not having the same width for a cell.

I want to maintain width and height as it is in original sheet.
and in my original sheet there are some hidden cells, but in my new sheet
those values are displaying but these values must not be shown these must
be hidden.

how can i do this can you please help me.

Thanx
Jaffar


"Richard Buttrey" wrote in
message ...
On Sat, 6 May 2006 13:02:34 -0700, "Yo****ha"
wrote:

Hi
i'm new to writing macros for an excel file.

i've a macro which will read the data from worksheets and copies that
data
and paste the copied data into new workbook sheets.

it is copying the data into new work sheet. whats my problem is my
original
application contains formatted data like having cloros and some of the
columns having big font size etc and some of sheets contains merged
cells.
i'm totally having 8 worksheets with different layouts and the macro
copying
the data into 8 new worksheets but the look and feel is not similar to
the
original application. But i want as it is.
cna anyone tell me how to do this
its very urgent for me

thanx in advance
jaffar

It sounds like the macro is just copying the values from the original
worksheets, and not the formatting as well.

Look for a line in the macro which says something like

Range("your_range").PasteSpecial (xlPasteValues)

If you want your new sheets to have exactly the same formulae, numbers
and formats as the old sheets, then change that to:

Range("your_range").PasteSpecial (xlPasteAll)

If you want your new worksheets to replace formulae in the old with
just values in the new, add an extra line:

Range("your_range").PasteSpecial (xlPasteFormats)






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________