davis1118
06-18-2018, 08:58 PM
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
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