Insert Method Of Range Class Failed Error

[USAMax]

I have found a lot of solutions for my problem, the strange thing is that when I change the line it works but when I run it again it fails until I change the line again. If this makes no sense good because it doesn't make sense to me either. I have done this a million times and it has always worked.


Here are all of my solutions, one will work then another and so on...

PLEASE SOMEONE WHY ISN'T IT CONSISTANT?

 Sheets(ActiveSheet.Name).Columns(intCurCol).Insert Shift:=xlToRight, _
                 CopyOrigin:=xlFormatFromLeftOrAbove        'Adds a column before the Revenue column

    Set wsThis = ActiveSheet                                '          Select the active worksheet
    wsThis.Select
    wsThis.Columns(intCurCol).Insert Shift:=xlToRight, _
                 CopyOrigin:=xlFormatFromLeftOrAbove        'Adds a column before the Revenue column

    Set wsThis = ActiveSheet                                '          Select the active worksheet
    wsThis.Select
    wsThis.Columns(intCurCol).select
    Selection.Insert Shift:=xlToRight, _
                 CopyOrigin:=xlFormatFromLeftOrAbove        'Adds a column before the Revenue column

    Activesheet.select
    ActiveSheet.Cells(1, intCurCol).EntireColumn.Insert Shift:=xlToRight, _
                 CopyOrigin:=xlFormatFromLeftOrAbove        'Adds a column before the Revenue column

    wsThis.Select
    wsThis.Cells(1, intCurCol).EntireColumn.Insert Shift:=xlToRight, _
                 CopyOrigin:=xlFormatFromLeftOrAbove        'Adds a column before the Revenue column

I believe I have tried every combination, I haven't been so frustrated in a very long time!

 

[USAMax]

I was adding more options or methods to insert a new column when I pressed F8 to run the last command that failed and the command worked the second time. My thought is that there has to be some delay?

I hate this solution so I could use some help.

    intTemp = 0
InsertColumn:
    On Error GoTo AfterColumn
    wsThis.Cells(1, intCurCol).EntireColumn.Insert Shift:=xlToRight, _
                 CopyOrigin:=xlFormatFromLeftOrAbove        'Adds a column before the Revenue column
    On Error GoTo 0
    intTemp = intTemp + 1
    If intTemp >= 10 Then End
    Application.Wait Now + TimeValue("00:00:10")
    GoTo InsertColumn
AfterColumn:

 

[USAMax]

My solution still did not work so I very much need some help.

I need a way to insert a column successfully... every time!

Please, anyone!

 

[USAMax]

SOLUTION!

I tried to insert a column before the specific data like this:

    wsThis.Range(wsThis.Cells(1, intCurCol), wsThis.Cells(lngLstRow, intCurCol)).Insert Shift:=xlToRight ', CopyOrigin:=xlFormatFromLeftOrAbove

This did not work so I tried to insert the column after the header row and it worked! The problem was that the AutoFilter was on.

For those who don't have a routine to turn AutoFilter on and off:

Public Sub SetAutoFilters(ws As Worksheet, OnOff As Boolean)
'Call SetAutoFilters(RawDataPM, True)
    Application.DisplayAlerts = False                       'Disable alerts
    With ws                                                 'Worksheet to update
        If OnOff Then                                       '     Does user want the AutoFilter On?
            If .AutoFilterMode Then                         '     YES: Is the Auto Filter enabled?
            Else
                Selection.AutoFilter                        '          YES: Are there filters on the Active Sheet?
            End If                                          '          END
        Else                                                '      NO: THE USER WANTS THE AUTOFILTER OFF
            If .AutoFilterMode Then                         '          Is the Auto Filter enabled?
               Selection.AutoFilter                         '          YES: Are there filters on the Active Sheet?
            End If                                          '          END
        End If                                              '     END
    End With                                                '
    Application.DisplayAlerts = True                        'Enable alerts
End Sub

 

[Rick Rothstein]

SOLUTION!

I tried to insert a column before the specific data like this:

    wsThis.Range(wsThis.Cells(1, intCurCol), wsThis.Cells(lngLstRow, intCurCol)).Insert Shift:=xlToRight ', CopyOrigin:=xlFormatFromLeftOrAbove

This did not work so I tried to insert the column after the header row and it worked! The problem was that the AutoFilter was on.

For those who don't have a routine to turn AutoFilter on and off:

Public Sub SetAutoFilters(ws As Worksheet, OnOff As Boolean)
'Call SetAutoFilters(RawDataPM, True)
    Application.DisplayAlerts = False                       'Disable alerts
    With ws                                                 'Worksheet to update
        If OnOff Then                                       '     Does user want the AutoFilter On?
            If .AutoFilterMode Then                         '     YES: Is the Auto Filter enabled?
            Else
                Selection.AutoFilter                        '          YES: Are there filters on the Active Sheet?
            End If                                          '          END
        Else                                                '      NO: THE USER WANTS THE AUTOFILTER OFF
            If .AutoFilterMode Then                         '          Is the Auto Filter enabled?
               Selection.AutoFilter                         '          YES: Are there filters on the Active Sheet?
            End If                                          '          END
        End If                                              '     END
    End With                                                '
    Application.DisplayAlerts = True                        'Enable alerts
End Sub

First off, I do not believe you need to turn DisplayAlerts off for this code as I cannot seem to make it generate any alerts. Second, I believe this one-liner subroutine does the same thing as your code above does...

[table="width: 500"]
[tr]
	[td]Public Sub SetAutoFilters(ws As Worksheet, OnOff As Boolean)
  If OnOff Xor ws.AutoFilterMode Then Selection.AutoFilter
End Sub[/td]
[/tr]
[/table]

 

[USAMax]

Thanks Rick, your advice has always been dead on. I have inserted columns before but it just will not work with my current document unless I turn off AutoFilter. It may be a one in a million but it is bound to happen to someone else as well.

 

[Rick Rothstein]

Thanks Rick, your advice has always been dead on. I have inserted columns before but it just will not work with my current document unless I turn off AutoFilter. It may be a one in a million but it is bound to happen to someone else as well.

I am sorry but I am not following... are you saying my code works differently than yours in some way? If yes, can you describe the exact set up so that I can duplicate the actions here in order to see why?

 

[USAMax]

Hey Rick,

I was not referring to your code but my situation. Your code was for the autofilter and I was referring to the fact that I cannot insert a column in MY current workbook with the autofilter on. I would live to share it with you so you can see what I am seeing but it belongs to the company I work for and I cannot share it. I agree, I have inserted columns before with the autofilter on but with this one workbook it will not work.

Thank you for your autofilter routine, you are right it is much simpler.

 

[Rick Rothstein]

...and I was referring to the fact that I cannot insert a column in MY current workbook with the autofilter on.

I just tried it in my copy of XL2010 and I can insert columns before and within the filtered range with any problem. There is one about inserting internally to the range... the inserted column gets its own drop down button. Filtered ranges appear to act like Excel Table objects do in that respect (probably because, I am guessing, they share common underlying code functionalities).