method range of object '_Global' failed trying to modify text

I have tried several different ways of removing the first 5 characters of text that is in column I and displaying it in column H, but I keep getting the range of object error:

Sub TRIM_SSP_CORE()
           
    With Sheet8.Range("A:B,E:J,L:AK,AN:AQ,AS:AW,AY:BF,BH:BH,BJ:BM,BO:BO")
        .Columns.Delete shift:=xlToLeft
    End With
    
    With Sheet8
        .Columns("I:I").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("H:H").Hidden = True
    End With
    
    With Sheet8
        .Range("I2:I").value = Left(Range("H2:H").value, -5)
    End With

End Sub

I have even tried this:

Sub TRIM_SSP_CORE()

Dim i As Integer
Dim TN As String

    With Sheet8.Range("A:B,E:J,L:AK,AN:AQ,AS:AW,AY:BF,BH:BH,BJ:BM,BO:BO")
        .Columns.Delete shift:=xlToLeft
    End With

    With Sheet8
        .Columns("I:I").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("H:H").Hidden = True
    End With

    With Sheet8
        For i = 2 To 2000
            TN = Range("I" & i)
            Range("H" $ i) = Left(TN, Len(TN) - 5)
        Next i
    End With

End Sub

Any guidance would be appreciated.

Solution

There are at least two issues with your code:

Negative length in left function: The Left function syntax is Left(String, Length), where Length must be a positive integer. Using -5 as the length is invalid and will cause an error.
Range handling: When you reference Range("H2:H"), you're selecting an entire column from row 2 downwards. Assigning Left(Range("H2:H").Value, 5) directly to another range doesn't work because Left expects a single string, not an array of values from multiple cells.

You can try something like:

With Sheet8

        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        
        ' Trim the first 5 characters from column H and place in column I
        For Each cell In .Range("H2:H" & lastRow)
            If Len(cell.Value) >= 5 Then
                cell.Offset(0, 1).Value = Mid(cell.Value, 6) ' Remove first 5 characters
            Else
                cell.Offset(0, 1).Value = "" ' Handle cases where text length is less than 5
            End If
        Next cell
End With