Excel VBA to create a named range

[Bosvark@]

Excel 2016 macro workbook.

I have a number of client lists based on the month they were activated. I need to create a named range for every month and want to minimise the number of time i spend to manually update the ranges.

I created the following to create the named range:

With ActiveWorkbook.Names
'all the past months manually added
.Add Name:="rngMHClients_Dec15", RefersTo:="='MHealth Clients'!$A$29789:$AY$32513"
End With

The row number after the Column "A" and "AY" changes depending on the month. How can i use a variable to do something like this:
.Add Name:="rngMHClients_Dec15", RefersTo:="='MHealth Clients'!$A$(variable):$AY$(Variable)"

Hope someone can help...

Thanks in advance.

 

[rmoosa]

Try something like this; you can easily use Variables in this notation.

With ActiveWorkbook.Names

Dim myRange As Range
Dim myWorksheet As Worksheet
Set myWorksheet = Sheets("MHealth Clients")
myWorksheet.Activate
Set myRange = myWorksheet.Range(myWorksheet.Cells(2, 2), myWorksheet.Cells(6, 5))
myRange.Select
'all the past months manually added
.Add Name:="rngMHClients_Dec15", RefersTo:=myRange
End With

 

[Bosvark@]

Try something like this; you can easily use Variables in this notation.

With ActiveWorkbook.Names

Dim myRange As Range
Dim myWorksheet As Worksheet
Set myWorksheet = Sheets("MHealth Clients")
myWorksheet.Activate
Set myRange = myWorksheet.Range(myWorksheet.Cells(2, 2), myWorksheet.Cells(6, 5))
myRange.Select
'all the past months manually added
.Add Name:="rngMHClients_Dec15", RefersTo:=myRange
End With

Thanks! so Quick. Will try it now.