VBA to automatically adjust cell height to separate worksheet where data is compiled

[dkman718]

Hello Mr. Excel Community,

I am running into a block that I am hoping someone can assist with to resolve; I have a spreadsheet that currently utilizes VBA to copy rows from one worksheet (let's call it SHEET #1) to another (SHEET #2) based on a response of YES to a drop down. This works perfectly for our needs and does not need to change, however I have recently added a new wrinkle where some of the copy-and-paste fields on SHEET #2 compile to a totally unrelated SHEET #3 automatically by way of basic formula (='SHEET #2!'whatever row/cell is involved); these fields are specifically text entries and I am hoping to automatically autofit the height of the rows on SHEET #3 to properly display all of the text information from SHEET #2, but every bit of VBA coding I have tried has failed to adjust the row heights at all.

If the reason the extra layer of coding won't work is that the "text entered" on SHEET #2 comes from a macro sending it over from SHEET #1, then I will resign myself to having the user manually resize the rows on SHEET #3 but I am willing to bet there is someone here that has encountered this before. Just in case, there is NO CELL MERGING going on anywhere as I had to remove all of that when the original macro was created; I also have manually selected AUTOFIT HEIGHT and WRAP TEXT options on all applicable cells beforehand.

Appreciate the help!!

 

[Gokhan Aycan]

As an example this works for me. One.A1 comes from VBA code, Two.D7 formula is "=One!A1"

Private Sub test_adjust_cell_height()
   
    Dim str As String
   
    str = "asnas sags ieie iiw iueu iieuwuew" & Chr(10) & "bdv huuushdud ugdu uddu usdug ugsg dguu udusd" & Chr(10) & "sdkd isd  hsdhid ih dishd dihd hiiihsdhhdi iishd"
   
    Worksheets("one").Range("A1").Value = str
    Worksheets("two").Range("D7").WrapText = True
    Worksheets("two").Range("D7").EntireRow.AutoFit
   
End Sub

 

[dkman718]

This unfortunately does not seem to work either, although I may be dropping it in to VBA in the wrong spot and/or modifying the variables incorrectly (if I was even supposed to do so - was unsure about the statement "One.A1 comes from VBA code, Two.D7 formula is "=One!A1"" above). Also, does the run of characters after 'str=' need to be changed or remain the same?

Just to clarify, the actual name for SHEET #1 is INITIAL REQUEST and for SHEET #2 is PHYSICAL SITE REQUEST; I am somewhat of a novice when it comes to VBA so if there are steps I am missing would appreciate anyone clarifying further.

Thanks!

 

[Gokhan Aycan]

Yeah, sorry for misleading. You need to change sheet names accordingly. Above names are what I have set in test workbook.

 

[dkman718]

This unfortunately does not seem to resolve the issue either; the data copies over just fine from SHEET #2 to SHEET #3 but will not autofit on SHEET #3 no matter what variations I try to the above code or via formatting changes.

Open to any suggestions at this point, thanks!