Copy column widths and row heights

 1999-12-20    Worksheets    0    215

With the macros below you can copy column widths and row heights from one range to another.

Private Sub CopyRowHeigths(TargetRange As Range, SourceRange As Range)
Dim r As Long
    With SourceRange
        For r = 1 To .Rows.Count
            TargetRange.Rows(r).RowHeight = .Rows(r).RowHeight
        Next r
    End With
End Sub

Private Sub CopyColumnWidths(TargetRange As Range, SourceRange As Range)
Dim c As Long
    With SourceRange
        For c = 1 To .Columns.Count
            TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
        Next c
    End With
End Sub
Example:
CopyColumnWidths(Range("E1:H1"), Range("A1:D1"))
CopyColumnWidths(Worksheets("Sheet2").Range("A1:D1"), _
    Worksheets("Sheet1").Range("A1:D1"))