Named Range Help - Excel 2013

[davis1118]

I am setting up an excel addin file to print to PDF and backup the file in an archive folder. The addin first prompts the user with a userform to ask how they want the file to be saved,i.e. Entire Workbook or Active Sheet, PDF save name, etc. I wanted the userform to remember the previously picked options for either the individual sheet or entire workbook. To do this I create 4 named ranges and place values in the ranges for the userform to look at when it loads. The named ranges are dynamic with the Worksheet Codename + the specific range name. This seems to work most of the time. But on a few occasions, the named ranges don’t have the worksheet codename. Without the sheet codename, the range names from sheet to sheet are identical and write over each other. If I put a break in my code to check if the sheet codename is set as a variable, it displays correctly in the code, and then works perfectly after that.

The code first deletes any named range (if it exists),and deletes the value in the range. Then it creates the named range and hidesthe row. The only reason I delete any named ranges first is to make sure ifrows or columns were added, that the named ranges are always in the same place.This might not be needed, and I’m definitely open to a better way to do this. I’mjust not sure if the current method I’m using is not the best way, which is whymy range names are not being named correctly. Below is the part of the code relating to the range names. I can include more code if necessary, but it is just printing to PDF and creating footers.

On Error GoTo lbl_Error2

folderPath = Application.ActiveWorkbook.Path
myName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".") - 1))
ext = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStrRev(ActiveWorkbook.Name, "."))
backupdirectory = "Archive"
T = Format(Now, "ddMmmyy")
shtnm = ActiveSheet.Name

On Error GoTo lbl_Error

frmSaveMethod.Show
Set myform = frmSaveMethod
Select Case myform.Tag
Case 1
    customname = LTrim(myform.TextBox3.Value)
'*******************************
'ACTIVE SHEET
'*******************************
    If myform.ActiveSheetBtn.Value = True Then
        Set ws = ActiveWorkbook.ActiveSheet
        codenm = ws.CodeName
        arr = Array(codenm & "PrintWhat", codenm & "PdfFormat", codenm & "PdfSaveName", codenm & "CustomName")
        j = 1
        For i = LBound(arr) To UBound(arr)
            'Delete any existing named ranges
            On Error Resume Next
            ws.Range(arr(i)).Value = ""
            ws.Range(arr(i)).EntireRow.Hidden = False
            ActiveWorkbook.Names(arr(i)).Delete
            On Error GoTo lbl_Error
            'Create new named ranges
            ws.Range("HH45000").Offset(0, j).Value = ""
            ws.Range("HH45000").Offset(0, j).Name = arr(i)
            ws.Range(arr(i)).Offset(0, j).EntireRow.Hidden = True
            j = j + 1
        Next I

Thank you very much for the help!

[Jan Karel Pieterse]

Rather than naming cells I would simply use range names without reference to a cell but local to the sheet in question. Air code:

Option Explicit

Sub UpdateLocalNameWithData(sName As String, sData As String)
    Dim oNm As Name
    On Error Resume Next
    Set oNm = ActiveSheet.Names(sName)
    On Error GoTo 0
    If oNm Is Nothing Then
        Set oNm = ActiveWorkbook.Names.Add(Name:=sName, RefersTo:="=1")
    End If
    oNm.RefersTo = sData
End Sub

Sub Demo()
    UpdateLocalNameWithData "'" & ActiveSheet.Name & "'!PrintWhat", "All"
End Sub

To fetch data from such names you simply do:

replace(ActiveWorkbook.names("'" & ActiveSheet.Name & "'!PrintWhat").RefersTo,"=","")