VBA code for copying part of a sheet to another, with column widthsand formats

[Andrew]

Hello,
I'm trying to take a range from one
sheet(Range.cells(1,1),cells(10,10)) and copy it from sheet 1 to sheet
2. The copy is easy. But what I don't understand how to do is to
carry over with the copy all of the column widths and numeric
formats. Can someone please explain how this is done in VBA code?

thanks

 

[Paul C]

The column widths are a seperate Paste Special operation.

Something like this will work

Sub test()
Sheets("Sheet1").Activate
Range(Cells(1, 1), Cells(10, 10)).Copy
Sheets("Sheet2").Activate
Range(Cells(1, 1), Cells(10, 10)).Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Selection.PasteSpecial Paste:=xlPasteColumnWidths
End Sub

If you want values instead of formulas use xlPasteValuesAndNumberFormats
instead of xlPasteFormulasAndNumberFormats. Using xlPasteAll instead of
xlPasteFormulasAndNumberFormats will copy the fonts, shading etc (but still
not the column width, this has to be seperate)