Euro5
asked on
Named table range repeating
The following code runs well, but the "Domestic 2 Day" creates the same range as the "Domestic 2 Day AM".
I can't logically figure out why....can anyone help??
I can't logically figure out why....can anyone help??
Sub createrangenames()
createtableranges "Domestic First Overnight", "FO", 1
createtableranges "Domestic Priority Overnight", "PO", 1
createtableranges "Domestic Standard Overnight", "SO", 1
createtableranges "Domestic 2 Day AM", "E2AM", 1
createtableranges "Domestic 2 Day", "E2", 1
createtableranges "Domestic Express Saver", "ESP", 1
createtableranges "Domestic 1Day Freight", "F1", 2
createtableranges "Domestic 2Day Freight", "F2", 2
createtableranges "Domestic 3Day Freight", "F3", 2
createtableranges "Continental U.S. Ground", "GR", 3
End Sub
Sub createtableranges(title, codename, tbltype)
Dim tr As Range
Set tr = ActiveSheet.Cells.Find(title, ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
If tr Is Nothing Then
Exit Sub
End If
On Error Resume Next
If tbltype = 1 Then
ActiveSheet.Names.Add codename & "L_2", Me.Range(tr.Offset(3), tr.Offset(3).End(xlToRight))
ActiveSheet.Names.Add codename & "P_2", ActiveSheet.Range(tr.Offset(4), tr.Offset(4).End(xlToRight).Offset(1))
ActiveSheet.Names.Add codename & "_2", ActiveSheet.Range(tr.Offset(6), tr.Offset(6).End(xlToRight).End(xlDown))
ActiveSheet.Names.Add codename & "HW_2", ActiveSheet.Range(tr.Offset(160), tr.Offset(160).End(xlToRight).End(xlDown))
End If
If tbltype = 2 Then
ActiveSheet.Names.Add codename & "_2", ActiveSheet.Range(tr.Offset(7), tr.Offset(7).End(xlToRight).End(xlDown))
End If
If tbltype = 3 Then
ActiveSheet.Names.Add codename & "_2", ActiveSheet.Range(tr.Offset(6), tr.Offset(6).End(xlToRight).End(xlDown))
End If
On Error GoTo 0
End Sub
sample-data.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER