VBA Create Range Not Working - What Am I doing Wrong?

[Shiseiji]

As always, TIA to those who give of their time and expertise. I can't figure out why the range isn't being created. I expect it will be a flat forehead moment when someone else looks at it.

Ron

The KISS record a macro code is:

Range("J2:P2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="tbl_ws_Report", RefersToR1C1:= _
        "='Accounts Report'!R2C10:R15751C16"

What I typically use is a little simpler with all rows and all columns. This is erroring on the range statement.

Sub m_ws_Report_Ranges()
'20211006
    '
  With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "Making Report Ranges"
    .Volatile
    .DisplayAlerts = False
  End With 'Application
    '
    Dim ws_ReportLastRow          As Long
    Dim tbl_ws_Report             As Range
    Dim ThisWb                    As Workbook
    Dim ThisWs                    As Worksheet
    '
  Set ThisWb = ActiveWorkbook
  With ThisWb
  ws_summary.Activate
    Set ThisWs = ActiveSheet
'--Start delete old range names
    ThisWb.Names("tbl_ws_report").Delete
'--End delete old range names
'-- Start Name Report ranges --
  ws_report.Activate
  Set ThisWs = ActiveSheet
  With ThisWs
'--Note: column J is email records, no null cells, no formulas
  ws_ReportLastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
  Range("J2:P", ws_ReportLastRow).Name = "tbl_ws_Report"
'-- End Name Report ranges --
  End With 'ThisWs
  ws_summary.Activate
  With ThisWs
  Application.ScreenUpdating = True
  Range("A1").Activate
  ActiveWindow.SmallScroll down:=3
  ActiveWindow.SmallScroll down:=-3
  Range("A1").Activate
  End With 'ThisWS
  With Application
    .ScreenUpdating = True 'False
    .EnableEvents = True 'False
    .DisplayAlerts = True ' False
    .Calculation = xlCalculationAutomatic 'xlManual
    .StatusBar = " "
    .StatusBar = "Completed creating Report Ranges."
  End With 'Application
  End With 'ThisWb
End Sub

 

[rlv01]

You have no Names.Add method in your code.

Not tested:

'--Note: column J is email records, no null cells, no formulas
  ws_ReportLastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
  'Range("J2:P", ws_ReportLastRow).Name = "tbl_ws_Report"
  ThisWb.Names.Add Name:="tbl_ws_Report", RefersTo:=.Range("J2:P", ws_ReportLastRow)
'-- End Name Report ranges --

 

[PeteWright]

Your code immediately throws an error, see screenshot.

 

[Shiseiji]

Your code immediately throws an error, see screenshot.View attachment 48518
ws_Summary is a sheet code name. Sorry, wouldn't have been obvious.

 

[Shiseiji]

You have no Names.Add method in your code.

Not tested:

'--Note: column J is email records, no null cells, no formulas
  ws_ReportLastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
  'Range("J2:P", ws_ReportLastRow).Name = "tbl_ws_Report"
  ThisWb.Names.Add Name:="tbl_ws_Report", RefersTo:=.Range("J2:P", ws_ReportLastRow)
'-- End Name Report ranges --

Thanks, no joy. Not that names.add may be a best practice I'm happy to use, just an FYI, I've been using code like the following for years without using names.add
I try to use worksheet code names, like the ws_report or ws_summary, but the sheet below has been imported from another workbook and will be deleted after some of the records are copied. Just easier for me to code in the workbook than trying to ensure I get the correct ranges from the closed workbook.

Sheets("New Badge Report").Activate
    With ThisWs
    NewBadgesLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    NewBadgesLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A2", Cells(NewBadgesLastRow, NewBadgesLastCol)).Name = "tbl_NewBadges"

 

[rlv01]

What error are you getting when you use my code?

 

[rlv01]

Try this instead

  ThisWb.Names.Add Name:="tbl_ws_Report", RefersTo:=Range("J2:P", ws_ReportLastRow)

 

[Shiseiji]

What error are you getting when you use my code?

My apologies, poorly worded . No error is thrown, but the range isn't added. Not listed in the Name Manager.

 

[Rick Rothstein]

Are you trying to give Range("J2:P2") the name "tbl_ws_Report"? If so and assuming the sheet it is on is active...

Range("J2:P2").Name = "tbl_ws_Report"

If the sheet is not active, just make the Range command reference it directly. For example, assuming Sheet1...

Sheets("Sheet1").Range("J2:P2").Name = "tbl_ws_Report"

 

[rlv01]

Looking at it again, your original line

Range("J2:P", ws_ReportLastRow).Name = "tbl_ws_Report"

may have a typo. Try this instead

Range("J2:P" & ws_ReportLastRow).Name = "tbl_ws_Report"