Adjusting Column Height/Width using VBA

[JP801]

Hey everyone!

I am running into an issue pasting a section from one sheet to another and the column height/width is not adjusting accordingly. The section pops up when I press a specific radio button but it's pretty spread out since the columns aren't adjusting. I have attached some photos to give you an idea. Section1 is the form the end user will be using. The Mileage sheet is where the data is coming from and what I want it to look like on Sheet1.

I must note, I am on my 4th day using VBA and have no clue what I am doing. Any help would be appreciated!

This is what my code currently looks like:

Private Sub OptAdd_Click()
Sheet2.UsedRange.Copy
Sheet1.Range("C11").PasteSpecial xlPasteAll
End Sub

Private Sub OptEdit_Click()
Sheet3.UsedRange.Copy
Sheet1.Range("C11").PasteSpecial xlPasteAll
End Sub

 

[jkpieterse]

You could try a paste special, column widths? Unfortunately, there is no such option to paste row heights. You'll have to traverse through all rows and set their heights.

 

[JP801]

You could try a paste special, column widths? Unfortunately, there is no such option to paste row heights. You'll have to traverse through all rows and set their heights.

That worked perfect for the widths. Is there a specific set of codes you recommend to adjust the heights?

 

[jkpieterse]

Something like this?

Private Sub OptAdd_Click()
    Dim rw As Range
    Sheet2.UsedRange.Copy
    Sheet1.Range("C11").PasteSpecial xlPasteAll
    Sheet1.Range("C11").PasteSpecial xlPasteColumnWidths
    For Each rw In Sheet2.UsedRange.Columns(1).Cells
      Sheet1.Range(rw.Address).Offset(10,2).EntireRow.RowHeight = rw.EntireRow.RowHeight
    Next
End Sub

The .Offset(10,2) is there because apparently you are not pasting in A1.

 

[JP801]

Something like this?

Private Sub OptAdd_Click()
    Dim rw As Range
    Sheet2.UsedRange.Copy
    Sheet1.Range("C11").PasteSpecial xlPasteAll
    Sheet1.Range("C11").PasteSpecial xlPasteColumnWidths
    For Each rw In Sheet2.UsedRange.Columns(1).Cells
      Sheet1.Range(rw.Address).Offset(10,2).EntireRow.RowHeight = rw.EntireRow.RowHeight
    Next
End Sub

The .Offset(10,2) is there because apparently you are not pasting in A1.

Thank you! This did the job!