Hi Wayne,
im not entirely sure what you are wanting to do.
Let me try to understand.
You want to be able to copy a dynamic range (one that can increase when additional rows are added) from Entry worksheet and then paste them to Data sheet. But you want the different ranges on the data sheet to have names.

Problem being is if your original data increases in size when you copy and paste to the data sheet, the starting cells may change.
Therefore the only thing i can suggest is to delete the data sheet each time and then paste the new data. The starting cells can be calculated by finding how many rows of data in the Entry data being copied, add say 4 rows of blank space, and then paste next data.

Or alternatively, count rows of data being copied, if more than the range on the data sheet then add aditional rows before pasting data?


i am finished now until about same time tomorrow, but if you can give me a bit more info or a better example of what you want to happen i will take another look.

heres a bit of code i found that might help you to set some range names

Sub Addnames()
     'add names for lookups
    Set ws = Worksheets("Data")
    With Worksheets("Data")
        Set rTable1 = ws.Range("A1:A", ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
        Set rTable2 = ws.Range("G2:G", ws.Range("G" & ws.Rows.Count).End(xlUp).Row)
        Set rLookup = ws.Range("G1:G", ws.Range("H" & ws.Rows.Count).End(xlUp).Row)
    End With
     
    rTable1.Name = "table1"
    rTable2.Name = "table2"
    rLookup.Name = "LookupTable"
     
End Sub
You can change the sheet names, colums and range names as required.