Insert row & copy formulas with wierd formatting

[danishpastry]

Hi everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've been rummaging around the web for a macro that inserts a new row at the bottom of a table and copies over the formulas -- however due to the layout (formatting) of the sheet the macro tries to add rows below the title. <o></o>
<o></o>
Basically, the macro I found below is exactly what I want, the rows get added automatically, and with a choice of how many. However, I need to be able to specify where the table begins.

I know the most logical solution is to change the formatting, but hopefully this is an easy edit for you guys! Thanks in advance!<o></o>

Sub InsertRowsBelowCopyFormulas()
'========================================================================
' THIS WILL ASK FOR THE NUMBER OF ROWS TO INSERT, THEN SELECT THE LAST ROW,
' COPY DOWN THE SELECTED NUMBER OF ROWS (WITH FORMULAS), THEN DELETE THE
' CONSTANTS IN THE NEW LINES COPIED DOWN
'========================================================================
Rng = InputBox("Enter number of rows required.")
If Rng = "" Then Exit Sub
On Error Resume Next
LastRow = Cells(Rows.Count, "a").End(xlUp).row
Range("A" & LastRow).EntireRow.Select
Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=Rng).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=Rng + 1), xlFillDefault
'THIS CLEARS ALL OF THE CONSTANTS IN THE NEW ROWS THEN COPIES DOWN THE VALUES
'OF THE NUMBER OF NEW ROWS IN COLUMN A
Selection.Offset(1).Resize(Rng).EntireRow.SpecialCells(xlConstants).ClearContents
ActiveCell.Select
Selection.AutoFill Selection.Resize(rowsize:=Rng + 1), xlFillDefault
End Sub

 

[AlphaFrog]

Try this...

Sub InsertRowsBelowCopyFormulas()
'========================================================================
' THIS WILL ASK FOR THE NUMBER OF ROWS TO INSERT, THEN SELECT THE LAST ROW,
' COPY DOWN THE SELECTED NUMBER OF ROWS (WITH FORMULAS), THEN DELETE THE
' CONSTANTS IN THE NEW LINES COPIED DOWN
'========================================================================
    Dim r As Integer, Lastrow As Long
    
    r = Application.InputBox("Enter number of rows required.", "No. of Rows", Type:=1)
    If r = 0 Then Exit Sub
    
    Application.ScreenUpdating = False

    Lastrow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Rows(Lastrow).AutoFill Rows(Lastrow).Resize(r + 1), xlFillDefault
    'THIS CLEARS ALL OF THE CONSTANTS IN THE NEW ROWS
    Rows(Lastrow).Offset(1).Resize(r).SpecialCells(xlConstants).ClearContents
    
    Application.ScreenUpdating = True
    
End Sub

If this isn't what you want, you'll have to better explain what you want and how your data is set up (formatted). Where exactly is the table.

 

[danishpastry]

Excellent, your coding + some tweaks solved my problem. Much appreciated!

*back to excel*