Paste Special

K

Kingstonsean

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?
 
K

Kassie

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.
 
T

Tom Ogilvy

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.
 
R

rberke

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

I had similar problem today. I wrote the following vba macro. It ha
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 shif
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 no
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 ro
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 fo
PasteSpecialRowHeights and
' pastespecialcolumnWidths to both use the sam
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
 
Top