If I copy a range of cells (either one row, or several rows and colums) from
one work sheet to another the shading, font, data, borders, etc, all copy
over to the new worksheet, but the column widths and row heights do not copy
over. It seems that in older versions of Excel (I'm using Office 2003) I
could use Paste Special|Formats, but this doesn't seem to work in Office
2003. Any suggestions?

Hi

You can actually copy the column widths, but you have to repaste as Special,
and then select Column Widths. So you do CtrlC, go to destination, right
click on Paste Special, select All, click on OK, right click on destination,
select Paste Special, click on column widths, and then on OK.

I do not know about row heights though.

"Kingstonsean" wrote:

If I copy a range of cells (either one row, or several rows and colums) from
one work sheet to another the shading, font, data, borders, etc, all copy
over to the new worksheet, but the column widths and row heights do not copy
over. It seems that in older versions of Excel (I'm using Office 2003) I
could use Paste Special|Formats, but this doesn't seem to work in Office
2003. Any suggestions?

Thanks. Sorta makes you wonder what "All" is for...


"Kassie" wrote:

Hi

You can actually copy the column widths, but you have to repaste as Special,
and then select Column Widths. So you do CtrlC, go to destination, right
click on Paste Special, select All, click on OK, right click on destination,
select Paste Special, click on column widths, and then on OK.

I do not know about row heights though.

"Kingstonsean" wrote:

If I copy a range of cells (either one row, or several rows and colums) from
one work sheet to another the shading, font, data, borders, etc, all copy
over to the new worksheet, but the column widths and row heights do not copy
over. It seems that in older versions of Excel (I'm using Office 2003) I
could use Paste Special|Formats, but this doesn't seem to work in Office
2003. Any suggestions?

Could never figure that one out myself! glad I coul have been of assistance

"Kingstonsean" wrote:

Thanks. Sorta makes you wonder what "All" is for...


"Kassie" wrote:

Hi

You can actually copy the column widths, but you have to repaste as Special,
and then select Column Widths. So you do CtrlC, go to destination, right
click on Paste Special, select All, click on OK, right click on destination,
select Paste Special, click on column widths, and then on OK.

I do not know about row heights though.

"Kingstonsean" wrote:

If I copy a range of cells (either one row, or several rows and colums) from
one work sheet to another the shading, font, data, borders, etc, all copy
over to the new worksheet, but the column widths and row heights do not copy
over. It seems that in older versions of Excel (I'm using Office 2003) I
could use Paste Special|Formats, but this doesn't seem to work in Office
2003. Any suggestions?

That didn't work any differently in older versions. The pastespecial
columnwidths as a separate option was added in xl2000 as I recall.

ColumnWidth is an attribute of the entire column. RowHeight is an attribute
of the entirerow. Since you are not copying the entirecolumn or entirerow,
that format is not copied.

--
Regards,
Tom Ogilvy

"Kassie" wrote in message
...
Could never figure that one out myself! glad I coul have been of
assistance

"Kingstonsean" wrote:

Thanks. Sorta makes you wonder what "All" is for...


"Kassie" wrote:

Hi

You can actually copy the column widths, but you have to repaste as
Special,
and then select Column Widths. So you do CtrlC, go to destination,
right
click on Paste Special, select All, click on OK, right click on
destination,
select Paste Special, click on column widths, and then on OK.

I do not know about row heights though.

"Kingstonsean" wrote:

If I copy a range of cells (either one row, or several rows and
colums) from
one work sheet to another the shading, font, data, borders, etc, all
copy
over to the new worksheet, but the column widths and row heights do
not copy
over. It seems that in older versions of Excel (I'm using Office
2003) I
could use Paste Special|Formats, but this doesn't seem to work in
Office
2003. Any suggestions?

Don't know if you are interested in vba solution, but if you are, read on.

I had similar problem today. I wrote the following vba macro. It has not been well tested, but it worked for me and I hope it helps you.

You can put into personal.xls.

I also have a macro in personal.xls which I have assign to ctlr shift n.

Sub askmacro()
s = Trim(LCase(InputBox("enter macro code")))
If Right(s, 1) = "s" Then s = Left(s, Len(s) - 1)
If s = "" Then Exit Sub

Select Case s
Case "pastediff": Call PasteSpecialHighlightDifference
Case "rtrim": Call myRtrimall
Case "paste45", "ps45": Call PasteHdgs45
Case "psrowheight": Call PasteSpecialRowHeights

Case Else
MsgBox s & "=no such shortcut"
End
End Select

End Sub


-------------------------------
Sub PasteSpecialRowHeights()
' Excel's EditPasteSpecial allows you to paste column widths, but not row heights.
' this macro exends that function
'
' to use macro:
' 1 format some rows to have your "ideal row heights"
' 2 select those full rows and copy them to the clipboard
' 3 navigate to the top left cell where you want to paste the row heights
' 4 call macro.
' the destination cells will now have the ideal heights
' future enhancement 1: I don't like requiring user to select full
' rows before they copy.
' this would make it possible for PasteSpecialRowHeights and
' pastespecialcolumnWidths to both use the same clipboard
' future enhancement 2: if target rectangle is more than one cell,
' restrict paste so only the selected rectangle is pasted
' future enhancement 3: bundle together 3 function:
' paste data
' paste row heights
' paste column widths
'
' to test current version quickly add the following steps
' select the ideal rows then Insert Name Define "testsrc"
' select the top left cell in your target area and Insert Name Define "testtgt"
' change constant to say "const testmode = true"

Const testmode = False
If testmode Then
Application.Goto ("testsrc")

Selection.Copy

Application.Goto ("testtgt")
End If

Set tgtsheet = ActiveSheet
Set tgtsel = Selection
Set tgtact = ActiveCell

ActiveWorkbook.Worksheets.Add
newsheetname = ActiveSheet.name

Set TempSheet = ActiveSheet
Selection.Insert Shift:=xlToDown


Dim c As Long

For c = 1 To TempSheet.UsedRange.Rows.Count
tgtsel.Rows(c).RowHeight = TempSheet.Rows(c).RowHeight
Next c

chgix = 0

If chgix = 0 Then
Application.DisplayAlerts = False
TempSheet.Delete
Application.DisplayAlerts = True
tgtsheet.Activate
tgtsel.Select
tgtact.Activate
Else
MsgBox chgix & " future use"
End If


End Sub