Creating a named range using the sheet name in an array sheet Loop - VBA

[SamNew2Coding]

I am trying to create a named range for multiple sheets from a specific cell to the last row. What i currently have:

Sub test123()
 Dim wsName As Variant, ws As Worksheet, LastRow As Long

 For Each wsName In Array("AA-CR", "AA-CP", "AAA-CR", "AAA-CP", "AAT-CP", "AAT-CR")
 Set ws = Worksheets(wsName)
 
 
 LastRow = ws.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 wsStringName = wsName
 ws.Range("A3:D" & LastRow).Name = wsName

 
 Next
 
End Sub

I have taken this mostly from other posts. I feel like i am close but it bugs when i run this line:

 ws.Range("B3:D" & LastRow).Name = wsName

I need some way to return the wsName value.

Bonus: Most sheets it will be "A3:D" and LastRow but for two of them it will be "B3:D" & LastRow, Is there an easy way to have an extra dimension where i can tell it to use this variable for two of them or do i have to just have another loop for the two different sheets?

 

[Fluff]

Hi & welcome to MrExcel.
How about

ws.Range("A3:D" & LastRow).Name = Replace(wsName, "-", "_")

It would probably just be easier to run another loop for the other two sheets.

 

[DanteAmor]

Hi and welcome to MrExcel.

In named range you cannot use the hypen, you must use the underscore:

Sub test123()
 Dim wsName As Variant, arr1 As Variant, arr2 As Variant
 Dim ws As Worksheet, LastRow As Long, i As Long
 
 arr1 = Array("AA-CR", "AA-CP", "AAA-CR", "AAA-CP", "AAT-CP", "AAT-CR")
 arr2 = Array("A", "B", "A", "B", "A", "A") 'Here indicates the starting column
 For i = 0 To UBound(arr1)
  wsName = arr1(i)
  Set ws = Worksheets(wsName)
  LastRow = ws.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  ws.Range(arr2(i) & "3:D" & LastRow).Name = Replace(wsName, "-", "_")
 Next
End Sub

 

[SamNew2Coding]

If only i knew how that the "-" was what was messing it up, i wouldve changed the sheetname and saved a fair bit of time! That worked wonders. Much Appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback.