Add Dynamic Named Ranges to Each Sheet (Excel 2013)

[davis1118]

Hello,
I'm creating dynamically named ranges based on the sheet code name. The code works perfectly when adding the 6 ranges to a single sheet, but I am struggling to get it to add the ranges to every sheet in the workbook. The code runs without errors and hides the row specified on each sheet, but the ranges are only named on the sheet that I initially run the code from with an active x button. All the other sheets don't have any named ranges. I'm pretty new to creating arrays and loops, so I'm sure I am missing a simple step, but without any errors I am a little confused. Thanks, for the help.

Private Sub CommandButton1_Click()
Dim arr()           As Variant
Dim i               As Long
Dim j               As Integer
Dim codenm          As String
Dim rng             As Range
Dim ws              As Worksheet

codenm = ThisWorkbook.ActiveSheet.CodeName
arr = Array(codenm & "PrintWhat", codenm & "PdfFormat", codenm & "PdfSaveName", codenm & "SelectionFrom", codenm & "SelectionTo", codenm & "CustomName")

With ThisWorkbook
    j = 1
    For Each ws In Worksheets
        ws.Select
        For i = LBound(arr) To UBound(arr)
            On Error Resume Next
            Set rng = .ActiveSheet.Range(arr(i))
            On Error GoTo 0
            j = j
            If Not rng Is Nothing Then
                rng.Value = ""
                .Names(arr(i)).Delete
                .ActiveSheet.Range("A2").Offset(0, j).Name = arr(i)
                .ActiveSheet.Range(arr(i)).Offset(0, j).EntireRow.Hidden = True
                j = j + 1
            Else
                .ActiveSheet.Range("A2").Offset(0, j).Value = ""
                .ActiveSheet.Range("A2").Offset(0, j).Name = arr(i)
                .ActiveSheet.Range(arr(i)).Offset(0, j).EntireRow.Hidden = True
                j = j + 1
            End If
        Next i
    Next ws
End With

End Sub

[p45cal]

does this do whaty you want:

Private Sub CommandButton1_Click()
Dim arr()           As Variant
Dim i               As Long
Dim j               As Integer
Dim codenm          As String
Dim rng             As Range
Dim ws              As Worksheet


With ThisWorkbook
  For Each ws In Worksheets
    'ws.Select
    codenm = ws.CodeName
    arr = Array(codenm & "PrintWhat", codenm & "PdfFormat", codenm & "PdfSaveName", codenm & "SelectionFrom", codenm & "SelectionTo", codenm & "CustomName")
    j = 1
    For i = LBound(arr) To UBound(arr)
      On Error Resume Next
      Set rng = Range(arr(i))
      On Error GoTo 0
      If Not rng Is Nothing Then
        rng.Value = ""
        '.Names(arr(i)).Delete'don't think you need this.
      Else
        ws.Range("A2").Offset(0, j).Value = ""
      End If
      ws.Range("A2").Offset(0, j).Name = arr(i)
      ws.Range(arr(i)).Offset(0, j).EntireRow.Hidden = True
      j = j + 1
    Next i
  Next ws
End With

End Sub

?

[davis1118]

Thank you very much p45cal! This works perfect now. I'm just curious why the named ranges weren't added when the .Name command line was inside the if statement? Both versions of the code seem logical to me, but yours definitely works much better, lol! Thanks again.

[p45cal]

They weren't being added because the names weren't changing from sheet to sheet, so the same 6 names were being applied to all sheets (which means there were only 6 names in the end because they were overwriting each other. It's the arr = Array(codenm & line which was brought within the For Each ws In Worksheets loop which made the changes.
The only reason I took the ws.Range("A2").Offset(0, j).Name = arr(i) out of the If statement was because in your code it was the same line executed regardless of which condition applied, so it didn't need to be conditional at all.
Also, you were using J=J which doesn't make a lot of sense to execute, and J just kept on getting bigger and bigger.

[davis1118]

Wow, that makes complete sense now that you explained it to me, and I have no idea why I had the j=j line in there. I think I was up too late messing around with it. Thank you for the explanation and cleaning up my code, much appreciated!