Trying to auto fit a row when sheet is not visible

[ABabeNChrist]

I’m having a problem trying to figure out a way so that when I use code to enter data from one sheet to another, so that the target cells will "EntireRow.AutoFit" to accommodate new entered data. I understand the target cell/sheet must be visible in order for sheet to AutoFit. Is there a way that so that I do not actually have to open target sheet.
At the present moment I use a userform with this code to enter selected data on to a sheet named Summary.

Code: Select all

Private Sub CommandButton1_Click()
'Water Heater System to Summary
    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False

    If CheckBox1.Value = True Then
        Sheets("Summary").Range("A199:A200").EntireRow.Hidden = False
        Sheets("Summary").Range("A199").Value = Range("M8").Value & _
                                                ": " & Range("A11").Value
        Sheets("Summary").Range("A200").Value = " • " & Range("A20").Value

    End If

    If CheckBox2.Value = True Then
        Sheets("Summary").Range("A201:A202").EntireRow.Hidden = False
        Sheets("Summary").Range("A201").Value = Range("M23").Value & _
                                                ": " & Range("A26").Value
        Sheets("Summary").Range("A202").Value = " • " & Range("A35").Value
    End If


    Sheets("Summary").Range("A198").EntireRow.Hidden = False


    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    Unload Me
    Unload UserForm18

End Sub

On the Summary sheet I have this code in place.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    For Each rng In Target.Rows
        Select Case rng.Row
        Case 1, 2, 3, 4    ' Rows that will be skipped
            ' Do nothing
        Case Else
            With rng
                .WrapText = True
                If .RowHeight < 21.5 Then
                    .RowHeight = 21.5
                Else
                    .EntireRow.AutoFit
                End If
            End Sub

But in order for the row to AutiFit I have to manually go to Summary sheet, click in selected cell and tab out.

[HansV]

You can autofit rows or columns in other sheets than the active sheet. Just specify the range correctly, i.e.

Worksheets("Other Sheet").Range("A3").EntireRow.AutoFit

[ABabeNChrist]

Okie Dokie
Thank you Hans
That seems easy enough