I'm new to VBA. I'm attempting to create a Name Range but i get runtime error 1004 Application - defined or object defined error on the line Set X = Worksheets("Sheet1").Range(Y)
Sub NameRangeAdd()
Dim X As Range
Dim Z As Integer
Dim RangeName As String
Dim Y As String
Z = Worksheets("Sheet1").Cells(1, 41).Value + 32
RangeName = "ValuesX"
Y = "Sheet1!$AD$" & Z & ":INDEX"Sheet1!$AD$" & Z & ":$BK$" & Z & ";COUNT"Sheet1!$AD$" & Z + 1 & ":$BK$" & Z + 1 & "))"
Set X = Worksheets("Sheet1").Range(Y)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=X
End Sub
Could you please help me ?
Sub NameRangeAdd()
Dim X As Range
Dim Z As Integer
Dim RangeName As String
Dim Y As String
Z = Worksheets("Sheet1").Cells(1, 41).Value + 32
RangeName = "ValuesX"
Y = "Sheet1!$AD$" & Z & ":INDEX"Sheet1!$AD$" & Z & ":$BK$" & Z & ";COUNT"Sheet1!$AD$" & Z + 1 & ":$BK$" & Z + 1 & "))"
Set X = Worksheets("Sheet1").Range(Y)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=X
End Sub
Could you please help me ?