Need to Shift the Entire worksheet down with Macro

[mojitoman6]

Hello,

I am currently using this macro to add a new row to the bottom of a named range (also copy formulas into this new row), and then rename the entire thing again (new row included)..

Sub AddNewRow()
    Dim nameOfRange As String
    nameOfRange = "Week1"


    With Range(nameOfRange)
        With .Rows(.Rows.Count).Resize(2)
            .FillDown
            Range(Range(nameOfRange), .Cells).Name = nameOfRange
        End With
    End With
End Sub

However, this is currently eating up everything below the named range as new rows are added (it will add rows on top of data below the named range)

Is there anyway i can somehow incorporate shifting the entire worksheet down when a new row is added to the named range? so the macro won't write over data below...?

 

[JoeMo]

This adds a new row below the named range before you expand the named range.

Sub AddNewRow()
Dim nameOfRange As String
nameOfRange = "Week1"
With Range(nameOfRange)
          .Rows(.Rows.Count).Offset(1, 0).Insert
          With .Rows(.Rows.Count).Resize(2)
                    .FillDown
                    Range(Range(nameOfRange), .Cells).Name = nameOfRange
          End With
End With
End Sub

 

[mojitoman6]

Worked! Well that was easy

Thank you

 

[JoeMo]

Worked! Well that was easy

Thank you

You are welcome - thanks for the reply.