List of Dates & Dynamic Name Range

[TheJonWithNoH]

Good Afternoon,

I currently have a list of dates starting in cell A2 which lists every day of the current year (1/1 - 12/31). I also have some named ranges that reference columns A:U for each month (Ex: January is A2:U32, February is A33:U63 etc) . I'm running into an issue where since next year is a leap year (2020), the named ranges are adjusted and throwing the named ranges off by a day.

Is there a way I can modify these named ranges dynamically so that they automatically adjust based on the date in column A?

Thanks in advance for any help you can provide!

JB

 

[Joe4]

How are you named ranges being set?

Are you re-using this each year, and if so, what are you doing when you reset for each year?

Are you simply clearing the entries (but not deleting the rows), so the named ranges still hold their places?
If so, what you will need to do is choose some date in the middle of February and insert a row there, and then the set named range for February will grow by one year.
Then, the following year, you will need to remember to delete a row in February.

Otherwise, you may want to create a macro that populates the dates and named ranges automatically each year.

 

[TheJonWithNoH]

To set the named ranges I go to Name Manager and set the ranges for the specific months (Ex: January is A2:U32).

To reset each year I am just modifying those named ranges but it's a tedious exercise.

I already have a macro that populates the dates, but not sure how to populate the named ranges.

I was hoping it would just be as simple as creating a dynamic named range somehow that if the date in column A is January, select columns A:U for those dates, etc.

 

[Joe4]

If you clear the data entries (and not actually delete the rows) each year, the named ranges would stay intact.
Then you wouldn't need to reset them every year. However, the leap year would still provide a challenge.
But as long as you already have a macro, you can use the macro to add the named ranges too.

However, before we do that, can you tell me what you are using the named ranges for?
They may actually not be necessary at all, depending on what you are using them for.

 

[TheJonWithNoH]

It's a pretty robust workbook and I'm not the best at explaining but I will give it a shot. Basically the named ranges are copied to another sheet based on user input on which specific month(s) are needed which is then exported to a new workbook as a data file and then imported to another workbook.

 

[Joe4]

OK, I was thinking you might be able to just use filters to pull the data that you need, but we can stick with what you what you are doing now.
So let's just try to build off what you have now.
Can you post the VBA code that you currently have to populate the dates?

And for your named ranges, are you just including column A or other columns in addition to it?

 

[TheJonWithNoH]

There's a lot of code that copies the dates but here's what populates the dates for January. This code repeats again for every following month for the year and it may seem a little complex but I'll explain what it does. Column AB has 4 values that repeat with every month in column D. Column AC has a list of months formatted as "mmm" dates. Column AD is a formula that calculates the number of days in the months in column AC.

Option ExplicitSub Macro1_CopyText_n_Dates()


Dim wsOutputTab As Worksheet
Dim bteNumOfDays As Byte
Dim rngMyCell As Range
Dim blnFirstText As Boolean
Dim i As Byte


Set wsOutputTab = Sheets("Forecast Data")

Application.ScreenUpdating = False

'January

For Each rngMyCell In wsOutputTab.Range("AC2")
If MonthName(Month(rngMyCell), True) = MonthName(Month(wsOutputTab.Range("AC2")), True) Then
bteNumOfDays = rngMyCell.Offset(0, 1)
Exit For
End If
Next rngMyCell
If bteNumOfDays = 0 Then
MsgBox "There was an error trying to assign the number of days for the month." & vbNewLine & "Please check and try again.", vbExclamation
Exit Sub
End If




blnFirstText = True

For Each rngMyCell In wsOutputTab.Range("AB2:AB" & wsOutputTab.Range("AB" & Rows.Count).End(xlUp).Row)
For i = 1 To bteNumOfDays
If blnFirstText = True Then
wsOutputTab.Cells(2, "A") = wsOutputTab.Range("AC2") + i - 1
wsOutputTab.Cells(2, "D") = rngMyCell.Value
blnFirstText = False
Else
wsOutputTab.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = wsOutputTab.Range("AC2") + i - 1
wsOutputTab.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = rngMyCell.Value
End If
Next i
Next rngMyCell

 

[TheJonWithNoH]

Also, the named ranges are from columns A:U... For example January is A2:U32

 

[Joe4]

OK, so it looks like you are starting on row 2, "rngMyCell" contains a date in the month, and "bteNumDays" holds the number of days in the month.
Then, you could use that information to set the named range like this:

    Dim rngName As String
    
    rngName = Format(rngMyCell, "mmmm")
    
    ActiveWorkbook.Names.Add Name:=rngName, RefersToR1C1:= _
        "=[COLOR=#ff0000]Sheet1[/COLOR]!R2C1:R" & bteNumDays + 1 & "C21"

Note that you will need to change the part in red to match whatever sheet you are putting this named range on.