Hopefully a quick and easy solution: I have the following code that automatically creates defined names within a certain tab. It works great, but Name Manager continues to show it as $D$56 for instance and I need it to be D$56. I've searched Mr Excel and and the internet but am not a VBA expert at all, so I'm not sure of the correct syntax or where to put it within this code...?
VBA Code:
Public Sub subCreateNamedRanges()
' https://www.mrexcel.com/board/threads/autofill-naming-defined-names.1234019/
Dim Ws As Worksheet
Dim strMsg As String
Dim rngRangeList As Range
Dim Rng As Range
Dim s As String
Dim NamedRange As Name
Dim strName As String
Dim blnSheet As Boolean
Dim rngAddress As Range
Dim intRow As Integer
Dim strColumns As String
Dim strCodes As String
Dim i As Integer
Dim arrColumns() As String
Dim arrCodes() As String
Dim WsList As Worksheet
Dim intCount As Integer
ActiveWorkbook.Save
strMsg = "Do you want to set the named ranges for the '" & ActiveSheet.Name & "' worksheet?"
If MsgBox(strMsg, vbYesNo, "Security Question") = vbNo Then
MsgBox "Activate the correct sheet before you run this code.", vbOKOnly, "Information"
Exit Sub
End If
Set Ws = ActiveSheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("NamedRangeList1234019").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "NamedRangeList1234019"
Set WsList = ActiveSheet
WsList.Range("A2:F20000").Cells.ClearContents
Ws.Activate
strColumns = "D"
arrColumns = Split(strColumns, ",")
strCodes = Replace("PCBMO", " ", "", 1)
arrCodes = Split(strCodes, ",")
For i = LBound(arrColumns) To UBound(arrColumns)
For intRow = 1 To 25
strName = arrCodes(i) & intRow
Set rngAddress = Ws.Cells(55 + intRow, Range(Trim(arrColumns(i)) & "1").Column)
With WsList
.Range("A" & Rows.Count).End(xlUp)(2) = strName
.Range("B" & Rows.Count).End(xlUp)(2) = "'" & Ws.Name & "!" & rngAddress.Address
End With
ThisWorkbook.Names.Add Name:=strName, RefersTo:=rngAddress
intCount = intCount + 1
Next intRow
Next i
ActiveWorkbook.Save
WsList.Activate
With WsList.Range("A1").CurrentRegion
.Font.Size = 16
.Font.Name = "Arial"
.EntireColumn.AutoFit
.VerticalAlignment = xlCenter
With .Rows(1)
.Value = Array("Name", "Address")
.Font.Bold = True
.Interior.Color = RGB(219, 219, 219)
End With
.RowHeight = 28
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = vbBlack
End With
.IndentLevel = 1
End With
WsList.Range("A2").Select
ActiveWindow.FreezePanes = True
strMsg = intCount & " named ranges have been created."
strMsg = strMsg & vbCrLf & "These have been listed in the " & WsList.Name & " worksheet."
MsgBox strMsg, vbInformation, "Confirmation"
End Sub
