Create Named Range for each column based on column heading text.

[anilsharaf]

Create Named Range for more than 10 columns data. RangeName Based on Column Heading by iterating throuh each column Heading Like Range.(Cells(1,col). And Range Reference to give by iterating throug each column from row 2 to Last row. Also Add these names to Thisworkbook.Worksheets("AllotedRollNos"). Data Table is:

Sitting Board_V1 Principle and Module InProcess.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sn Final according to Check List	Sort1 Medium Hindi 1 then English 2	Sort2 Type Regular 1 or Div Improve 2	Sort3 Center Number	Medium	Type	School Name Hss 2024	Sort Temp Continuous Roll No	RN from	RN To	sort4 according to strength for sitting Priority	Total Students	Sn From	Sn To
2	1	1	1	321042	Hindi	Regular	Barpali	1	2243203786	2243203830	3	45	01	45
3	2	1	1	321044	Hindi	Regular	Boys	2	2243203831	2243203899	1	69	46	114
4	3	1	1	321070	Hindi	Regular	Madwa	4	2243203946	2243203981	4	36	115	150
5	4	1	1	321224	Hindi	Regular	Pithampur	5	2243203982	2243204046	2	65	151	215
6	5	1	1	322292	Hindi	Regular	DVM	7	2243204115	2243204139	5	25	216	240
7	6	1	2	321044	Hindi	Div Improve	Boys	9	2243280021	2243280021	6	01	241	241
8	7	1	2	321070	Hindi	Div Improve	Madwa	8	2243299002	2243299002	7	01	242	242
9	8	2	1	321044	English	Regular	Atmanand	3	2243203900	2243203945	9	46	243	288
10	9	2	1	322071	English	Regular	Lions	6	2243204047	2243204114	8	68	289	356
11	10	2	2	322071	English	Div Improve	Lions	10	2243280022	2243280022	10	01	357	357
12					Tot							357
AllotedRollNos

Sub CreateNamedRanges_NotReady()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim Col As Long
    Dim TempRng As Range
    Dim Header As String
    Dim RangeName As String
    
    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Worksheets("AllotedRollNos") ' Change "AllotedRollNos" to your sheet name
    
    ' Find the last row in column A (assuming your data starts from row 2)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through columns A to V
    For Col = 1 To 22 ' Adjust the range as needed
        ' Get the header text from row 1
        Header = ws.Cells(1, Col).Value
        
        ' Create the range name
        RangeName = Header
        
        ' Define the range (from row 2 to LastRow)
        With ThisWorkbook.Worksheets("AllotedRollNos")
        'Set ws.Range(ws.Cells(2, Col), ws.Cells(LastRow, Col)).Name = RangeName
        Set TempRng = ws.Range(ws.Cells(2, Col), ws.Cells(LastRow, Col))
        PrintTest = "=" & TempRng.Address
        'ThisWorkbook.Names.Add Name:=RangeName, RefersTo:="A2:A11": 'ws.Range(TempRng.Address)"
        
        '********
        ActiveWorkbook.Worksheets("AllotedRollNos").Names.Add Name:= _
        RangeName, RefersTo:="=" & TempRng.Address
        '********
        End With
        
    Next Col
End Sub

I tried many variations. But at the time of test all failed. Line 33 between stars create runtime error 1004. Thanks in advance

 

[Cubist]

Try

       ActiveWorkbook.Worksheets("AllotedRollNos").Names.Add Name:= _
        RangeName, RefersTo:= TempRng

 

[anilsharaf]

Try

       ActiveWorkbook.Worksheets("AllotedRollNos").Names.Add Name:= _
        RangeName, RefersTo:= TempRng

It is again showing runtime error 1004

 

[Cubist]

Edited: The names are the issue.

• A name cannot be more than 255 characters long.
• You cannot use spaces in your names.
• The first letter can only be a letter, an underscore, or a backslash.
• You cannot use actual cell addresses as named ranges. For example, you cannot call your named range “A7”.
• You can use symbols such as forward slashes, underscores and dots in names, but you cannot use other symbols.

 

[anilsharaf]

Edited: The names are the issue.

• A name cannot be more than 255 characters long.
• You cannot use spaces in your names.
• The first letter can only be a letter, an underscore, or a backslash.
• You cannot use actual cell addresses as named ranges. For example, you cannot call your named range “A7”.
• You can use symbols such as forward slashes, underscores and dots in names, but you cannot use other symbols.

Excellent! Thank You so much. This worked fine. I replaced all spaces with underscore of column headings and it was solved. Like Below:

Sitting Board_V1 Principle and Module InProcess.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sn_Final_according_to_Check_List	Sort1_Medium_Hindi_1_then_English_2	Sort2_Type_Regular_1_or_Div_Improve_2_	Sort3_Center_Number_	Medium	Type	School_Name_Hss_2024	Sort_Temp_Continuous_Roll_No	RN_from	RN_To	sort4_according_to_strength_for_sitting_Priority	Total_Students	Sn_From	Sn_To
2	1	1	1	321042	Hindi	Regular	Barpali	1	2243203786	2243203830	3	45	01	45
3	2	1	1	321044	Hindi	Regular	Boys	2	2243203831	2243203899	1	69	46	114
4	3	1	1	321070	Hindi	Regular	Madwa	4	2243203946	2243203981	4	36	115	150
5	4	1	1	321224	Hindi	Regular	Pithampur	5	2243203982	2243204046	2	65	151	215
6	5	1	1	322292	Hindi	Regular	DVM	7	2243204115	2243204139	5	25	216	240
7	6	1	2	321044	Hindi	Div Improve	Boys	9	2243280021	2243280021	6	01	241	241
8	7	1	2	321070	Hindi	Div Improve	Madwa	8	2243299002	2243299002	7	01	242	242
9	8	2	1	321044	English	Regular	Atmanand	3	2243203900	2243203945	9	46	243	288
10	9	2	1	322071	English	Regular	Lions	6	2243204047	2243204114	8	68	289	356
11	10	2	2	322071	English	Div Improve	Lions	10	2243280022	2243280022	10	01	357	357
12					Tot							357
AllotedRollNos

Again Thanks!!!

 

[Cubist]

Glad that worked out. Cheers.