List of Dates & Dynamic Name Range

TheJonWithNoH

New Member
Joined
Sep 8, 2017
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 8, 2017
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 8, 2017
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 8, 2017
Messages
30
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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:
Code:
    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.
 

Forum statistics

Threads
1,077,827
Messages
5,336,611
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top