Adding row to a named range-updating the named range address

I have a file to keep records of RV park guest.
I use a row of data range named GuestIP where I enter the data for each new guest.

I then use the following commands to add the guest to range named Guest_DB.

Sheets("Guest-DB").Range("GuestIP").Copy
    Sheets("Guest-DB").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Sheets("Guest-DB").Range("c" & Rows.Count).End(xlUp).PasteSpecial xlPasteFormats

However this does not change the address of the Guest-DB to include the added guest.

ie Before adding the new guest the Guest-DB address is C6:AB564, after updating the address is still C6:AB564 needs to be C6:AB565.

vlookup commands else where in the workbook cannot find the new guest until I manually change the Address to include the row with the new guest.

Is there a way to change the range address when you add a row to the bottom of the range?

Any help would be appreciated.

Hi..

I then use the following commands to add the guest to range named Guest_DB.

Your sheet is named "Guest_DB".. so i assume that that is a 'typo'... and therefore assume your Named Range is called "GuestIP"?

Here's a way you can increase the size(by 1 row) of your Named Range...

ActiveWorkbook.Names.Add Name:="GuestIP", RefersToR1C1:= _
            .Range("GuestIP").Offset(1, 0)

It basically 're-adds' your named range and increases the row size by 1...
Does that make sense?

Ok, I tried that line as a stand alone to see what it did. I had to add sheets id in front of the .range to make it work. but that would solve my problems

Thank You very much
JWD

I used this script and it appeared to work.
However after several entries I discovered the Range address as not 'Adding' the new line it was shifting the Range name down by one line.
In a different file the Range name "ProLst" starts in cell B2 and ends in cell K79,
When I use the following commands:

'==============
Sheets("Formulas").Range("NuItem").Copy
Sheets("Lkups").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues  'Adding to Range - ProLst cells B2 thru K79
Sheets("Lkups").Range("b" & Rows.Count).End(xlUp).PasteSpecial xlPasteFormats
'------------------Re ID Ranges Names----------------
Sheets("Lkups").Select
Range("B2").Select
ActiveWorkbook.Names.Add Name:="ProLst", RefersToR1C1:= _
            Sheets("Lkups").Range("ProLst").Offset(1, 0)              ' Renaming range ProLst to be cells B2 thru K80
'--------

to add a new item to the list, the new range for 'ProLst' is now B3 thru K80, not the intended B2 thru K80.
I lose the first row of data in the file.

I need the range name to increase by one row of data, not just change the location of the range.

Any help would be appreciated.
Thank you

Not sure if that's what I'm looking for.

The Name "Guest-DB" was an error.
The worksheet sheet name is "Guest-DB", but
The range I want it to add to is located on worksheet "Guest-DB" range name is "GuestData".


GuestIP is a single row where I manually enter some of the information, other parts of the row calculate information. Once the information has been verified I add the values to the "GuestData" range.

I don't want the calculation "formulas" from the input range "GuestIP" in the output range "GuestData",
just the values.
I want each record in that, ("GuestData"), range unchanged after it's been added.

vlookup formulas else where in the workbook look up "GuestData" for information about a guest.

If the line you show just changed "GuestData" by one line and that all it does, I could use it.

Thank you
JWD

Hi.. yeap .. sorry.. that line of code would normally be used like:

With Sheets("SheetID")
ActiveWorkbook.Names.Add Name:="GuestIP", RefersToR1C1:= _
            .Range("GuestIP").Offset(1, 0)

End With

Change my code and this one worked
Thank You

Hi..

Oops..
I may have led you astray previously..

This will increase the range by 1 row..

With Worksheets("Lkups")
        ActiveWorkbook.Names.Add Name:="ProLst", RefersToR1C1:= _
        .Range("ProLst", .Range("ProLst").Offset(1, 0))
           
End With

Thank You, I'll try it today.