Why am I getting an Automation error Here?

[prabha_friend]

If .UsedRange.Columns.Count > 23 Then .Range(.Columns(24), .UsedRange.Columns(.UsedRange.Columns.Count)).EntireColumn.Delete shift:=xlLeft
If .UsedRange.Columns.Count > 23 Then
    .Name = .Name & "(1)"
    Set varTemp = .Parent.Sheets.Add(After:=.Parent.Sheets(.Name))
    varTemp.Name = Left(.Name, Len(.Name) - 3)
    .Activate   'I think activation is required here
    .Range(Columns(1), Columns(23)).Copy
    varTemp.Columns(1).Insert
    Excel.Application.DisplayAlerts = False
    .Delete
    Excel.Application.DisplayAlerts = True
    Set TempSheet = varTemp
End If
.UsedRange.Columns(1).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'Unique Column

I am getting the following error on the last statement of the above quoted codes:

Run-time error '-2147221080 (800401a8)':

Automation error

 

[RickXL]

Hi,

You have not shown all your code but my guess would be that it is objecting to you trying to remove rows from a worksheet you have just deleted.

If .UsedRange.Columns.Count > 23 Then .Range(.Columns(24), .UsedRange.Columns(.UsedRange.Columns.Count)).EntireColumn.Delete shift:=xlLeft
If .UsedRange.Columns.Count > 23 Then
    .Name = .Name & "(1)"
    Set varTemp = .Parent.Sheets.Add(After:=.Parent.Sheets(.Name))
    varTemp.Name = Left(.Name, Len(.Name) - 3)
    .Activate   'I think activation is required here
    .Range(Columns(1), Columns(23)).Copy
    varTemp.Columns(1).Insert
    Excel.Application.DisplayAlerts = False
    [B][COLOR=#ff0000].Delete[/COLOR][/B]
    Excel.Application.DisplayAlerts = True
    Set TempSheet = varTemp
End If
[B][COLOR=#ff0000].UsedRange.Columns(1).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/COLOR][/B] 'Unique Column