Hello,

Rather than naming 200 ranges, you could use a relative reference in the name definition. To do so, choose any cell on Sheet1 row 1 and define a name (FormulasDefine Name) and enter: =Sheet1!$J1,Sheet1!$V1,Sheet1!$Z1. This will keep the column references locked, but the row reference will change depending on what row the name is used in. So, in Sheet2 you could put "=SUM(Data)" in any cell on row 1 and copy it down 200 rows. The downside to this approach is that your formulas on Sheet2 must be entered on the same row as the data on Sheet1.

Alternatively, you could use the macro below to assign a unique name to each data row. Uncomment the line starting with "Sheet2..." to also add a sum referencing each new named range.

Sub CreateNames()
Dim rNames As Range
Dim lNames As Long
Dim sName As String

Set rNames = Sheet1.Range("J1,V1,Z1")
sName = "Data"

For lNames = 1 To 200

ThisWorkbook.Names.Add Name:=sName & lNames, RefersTo:= _
"=" & rNames.Offset(lNames - 1, 0).Address
'Sheet2.Range("A1").Offset(lNames - 1, 0).Formula = "=SUM(" & sName & lNames & ")"

Next lNames

End Sub