С помощью макросов ниже вы можете копировать высоту строк и ширину столбцов из одного диапазона в другой.

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

Пример:

CopyColumnWidths(Range("E1:H1"), Range("A1:D1"))

CopyColumnWidths(Worksheets("Sheet2").Range("A1:D1"), _

Worksheets("Sheet1").Range("A1:D1"))