XL2010 VBA Error - Method 'Insert' of object 'Range' failed

[TechTank]

Hi there,

I have a macro (code below) that is assigned to a button in an Excel 2010 Macro Enabled Template (.xlsm). I push the button and it copies a pre-formatted row from one worksheet to another and pastes it beneath a row with a specified value in a column.

Option Explicit
Sub Insert_Line_1_Pre_Deployment_Preparation()
    Dim FindString As String
    Dim Rng As Range

With ActiveSheet.Unprotect
        
'Enter Search Value between the ""
    FindString = "1"
    
    If Trim(FindString) <> "" Then

'Set The Search Range Between The ""
        With ActiveSheet.Range("A:A")
            Set Rng = .Find(What:=FindString, After:=.Cells(1, 1), LookIn:=xlValues, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                            MatchCase:=False)

'Moves To The Cell With The Value In
            If Not Rng Is Nothing Then
                Application.GoTo Rng, True

'Copys The Row Specified
                Sheets("Format Control").Rows(19).Copy
                
'Inserts The Row Copied Beneath The Row Found With The Value In
                Rng.Offset(1).EntireRow.Insert
                Rng.Offset(1).EntireRow.PasteSpecial
                
'Moves The Active Cell 1 Row Down and 2 Cells To The Right
                Rng.Offset(1, 2).Select
                
                Else
            End If
        End With
    End If
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows _
        :=True
            
    End With

ActiveWindow.SmallScroll Down:=-8
    
End Sub

It works perfectly as long as I don't delete a row and then want to use this button to copy\insert a pre-formatted row again. If I do I am presented with the following error box upon execution:

Microsoft Visual Basic

Run-time error '-2147417848 (80010108)':

Method 'Insert' of object 'Range' failed

This code works fantastic in Excel 2003 but we are about to migrate to Office 2010 (the error being in Execl 2010) and I really need the skills of some of the fantastic members on here to help me out.

Thank you for your time and any help,

Mark.

 

[venkat1926]

it is perfectly correct to RECORD a macro but you must edit it suitably

see the modified macro

Sub Insert_Line_1_Pre_Deployment_Preparation()
    Dim FindString As String
    Dim Rng As Range


    With ActiveSheet
    .Unprotect


        'Enter Search Value between the ""
        FindString = "1"


        If FindString <> "" Then


            'Set The Search Range Between The ""
            'With ActiveSheet
            With .Range("A:A")
                Set Rng = .Find(What:=FindString, After:=.Cells(1, 1), LookIn:=xlValues, _
                                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False)
                Rng.EntireRow.Copy


                End With
                With Worksheets("format control")
                    .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial


                End With
                End If
                 .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows _
        :=True
                
              End With


                
'                'Moves To The Cell With The Value In
'                '            If Not Rng Is Nothing Then
'                '                Application.GoTo Rng, True
'                '
'                ''Copys The Row Specified
'                '               Sheets("Format Control").Rows(19).Copy
'
'                'Inserts The Row Copied Beneath The Row Found With The Value In
'                '                Rng.Offset(1).EntireRow.Insert
'                '                Rng.Offset(1).EntireRow.PasteSpecial
'                '
'                'Moves The Active Cell 1 Row Down and 2 Cells To The Right
'                'Rng.Offset(1, 2).Select
'
'
'                'End If
'                'End With
'                'End If
'
'                .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
'                                                                                     , AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows _
'                                                                                                                                             :=True
'           'End If
''        End With
'
'
'        'ActiveWindow.SmallScroll Down:=-8
'        End If
'        End With
    End Sub

if you want to copy a row or range to another sheet you need not insert a row unless there is some data already in the second sheet and you want to copy paste the data in between.. even if there is data in second sheet and if you want to paste the data at the end of the existing data it is not necessary to insert
you can uses
cells(rows.count,"A"),end(xlup).offset(1,0)
this is the one cell after the last cell in column A

continue to RECORD macro but edit the resulting macro

one more suggestion in the find function in vba you need not repeat all of them because many of them are default(in other words if you do not include that argument the argument taken will be default argument).
in editing recorded macro profusely use the excel help. of course you can also refer your doubt to this newsgroup.
with best compliments

 

[TechTank]

Hi Venkat,

Thanks for the above, I'll certainly walk through it in a macro and see what's going on, always looking to improve my knowledge and thanks for looking into this for me.

 

[TechTank]

Hi Venkat,

OK, I've walked through the macro with F8 and the above copies a row from the sheet I was copying a row into from 'Format Control'. It's quite a specific thing I'm trying to do with this workbook so it might seem a bit obscure. I'll call the sheet I want the row copied into 'Data' for now. What this code does is:

In the 'Data' worksheet it looks for the last entry of the number 1 in Column A (There are different numbers for different sections in this worksheet).
It finds the number and it then goes to the 'Format Control' worksheet and copies a pre-formatted row from there.
It then inserts a new row under the last Cell in Column A to contain a '1' (this is to move any data that might be below in different sections down) before pasting this pre-formatted row on top of the newly inserted row.

I'm not sure if that helps to make things clearer but it works perfectly in 2003...just 2010 but handle parts of this code differently and I had help to put this code together from members on here last year.

Could it be the "With .Range("A:A")" that is breaking the code when I delete a row from the sections contain number 1's in Column A do you think?

Thanks for your time and patience.

 

[venkat1926]

some confusion in my mind.
are u taking about your macro or my modified macro
give some specific examples
1. when you enter in the row next to last row of data there is no INSERTING a row. the row is alrady there and you copy the data from A column of that row.

2. I do not know where the DELETION of a row comes
remember when you insert or delete a row through a macro DO NOT START FROM THE FIRST ROW. because when you delete any row the row no. will change and the loop will misbehave. start from the last row and go step -1 back this will take care of row number

j is the last row

for k=j to 1 step -1

something like this.

3. yes sometimes range("A:A") may give some problme

in that case try this method
dim r as range

set r=range(range("A1"),cells(rows.count,"A").end(xlup))

then use r insed of range("A:A")
try some experiments.

learning is by experimenting and doing mistakes and correcting them.