Add Additional Parts Range??

[klarowe]

I am trying to give the ability to easily add extra parts templates on this report by the use of a form button (not activex)
Basically each part has a range of about 6 rows (part name, part number, disposition, comments, etc).

Right now I have a "template" typed into Range A1000 to I10005 which is named "Extra Parts". In the code I have written, that range is unhidden, then copied, and supposed to be pasted after the last part on the sheet(offset of 2,0) I cannot use the end(xldown) because there are some blank cells for part names to be added so I have to use end(xlup) but even if I re-hide the "Extra Parts" Range it still sees that as input and inserts the additional template after that. I hope I'm making some sense here.

I even tried saying "Range("A999").End(xlup) in hopes that it would start from there but it didn't work.

Here is the code I have now:

Sub AddExtraParts()
    Dim RunCount As Long
    Const RunMax As Long = 10
    howmany = InputBox("Enter number of parts to be added.", "Add Additional Parts.")
    For RunCount = 1 To howmany
    Call AddExtraPartsSub
    Next
End Sub
Sub AddExtraPartsSub()
    With Sheets("Additional Parts")
        .Range("ExtraParts").EntireRow.Hidden = False
        .Range("ExtraParts").Copy
        .Range("ExtraParts").EntireRow.Hidden = True
        .Range("A65530").End(xlUp).Offset(2, 0).Insert Shift:=xlDown
    End With
End Sub

Any ideas? I also tried putting the template on a hidden sheet but that screws up multiple other codes I have so it is not really an option at the moment.

 

[klarowe]

I found a work around code that uses the .find feature to find where I have my heading for pictures and then offsets -2. It works great on all but 5 of my reports. The only difference in these 5 reports is that there are 2 sheets that I need to add this code to (through a form button) where-as the other reports only have 1 sheet that needs it.
here is the code I have:

Sub AddExtraParts()
    Dim RunCount As Long
    Const RunMax As Long = 10
    'On Error GoTo Er
    howmany = InputBox("Enter number of parts to be added.", "Add Additional Parts.")
    For RunCount = 1 To howmany
    Call AddExtraPartsSub
    Next
    MsgBox "Please insert rows as needed for proper page formatting.", vbOKOnly, "Re-format as needed."
    Exit Sub
    
Er: MsgBox "There was an error preventing additional parts from being added.", vbOKOnly, "Error"
    Exit Sub
End Sub
Sub AddExtraPartsSub()
    Range("ExtraParts").EntireRow.Hidden = False
    Range("ExtraParts").Copy
    Range("A1:A999").Find("Pictures").Offset(-2, 0).Select
    Selection.Insert shift:=xlDown
    Range("ExtraParts").EntireRow.Hidden = True
End Sub

It gives me an error of:
Runtime error '91
Object variable or With block variable not set

on line:

Range("A1:A999").Find("Pictures").Offset(-2, 0).Select

I don't understand why it is working fine on 16 other reports, just not these 5...