Send data from userform to named range

Hi All, I have a userform that is used to collect something like 75 pieces of data, which are then sent to the main worksheet. I have code which then parses the data into 5 named ranges for import into an Access database. Code for transferring the data looks like this:

'--------ClinicData--------
ws.Cells(lRow, 1).Value = Format(Me.TxtTxID & (lRow - 2), "0")
ws.Cells(lRow, 2).Value = Me.txtClinic
ws.Cells(lRow, 3).Value = Me.txtSurgeon
ws.Cells(lRow, 4).Value = Me.txtRep1
ws.Cells(lRow, 5).Value = Me.txtRep2


If Me.txtSN > 4 Then
    ws.Cells(lRow, 6).Value = Right(Me.txtSN, 4)
Else: ws.Cells(lRow, 6).Value = Me.txtSN
End If

ws.Cells(lRow, 7).Value = Me.txtSW
ws.Cells(lRow, 8).Value = Me.cboPhaco
ws.Cells(lRow, 9).Value = Format(Me.txtDate, "dd-mmm-yyyy")

Code to parse the data looks like this:

Sub Name_Ranges()

Sheets("Export").Unprotect Password:="CataractData"
ThisWorkbook.Names.Add Name:="aClinicData", _
    RefersTo:=Sheets("Export").Range("A1:I" & Sheets("Export").Range("A65535").End(xlUp).Row)

End Sub

So far this is working well. So well, in fact so well that Management now wants more data in the form. This will be a lot of work, because the new data will not be at the end of the list of data, but interspersed within the row. Therefore, if I add two items in the middle, then all of the other cell references will have to be changed by two as well.

I'm curious if it's possible that I reference the named range when inserting the data - that way at least when I am writing the code I can keep it straight in my mind that things are going in the right places!

By the way, this was originally posted here: http://www.mrexcel.com/forum/excel-q...ml#post4277811 ... no luck there...

Hi wpryan,
maybe so

Sub Name_Ranges()
Sheets("Export").Unprotect Password:="CataractData"
ThisWorkbook.Names.Add Name:="aClinicData", _
    RefersTo:=Sheets("Export").Range("A1").CurrentRegion
End Sub