Dynamically selecting cells in Excel Worksheet using VBA

Question

I'm creating a calendar of months. The user can select a year for the month they want. When a user selects to create a month, I want to select all the weekdays in that month and give it a named range. How can I select a range of cells for a selected month and year and give it a named range?

Here is the recorded macro (for Jan 2016), but I'm not sure how to create this programmatically. I cannot reuse it because what if the user picks a different month or year, then I won't know which row columns contain weekdays.

range( _
    "C9:D9,E9:F9,G9:H9,I9:J9,K9:L9,C11:D11,E11:F11,G11:H11,I11:J11,K11:L11,C13:D13,E13:F13,G13:H13,I13:J13,K13:L13,C15:D15,E15:F15,G15:H15,I15:J15,K15:L15,C17:D17,E17:F17" _
    ).Select
range("E17").Activate
ActiveWorkbook.Names.Add Name:="Test", RefersToR1C1:= _
    "=PTO!R9C3,PTO!R9C5,PTO!R9C7,PTO!R9C9,PTO!R9C11,PTO!R11C3,PTO!R11C5,PTO!R11C7,PTO!R11C9,PTO!R11C11,PTO!R13C3,PTO!R13C5,PTO!R13C7,PTO!R13C9,PTO!R13C11,PTO!R15C3,PTO!R15C5,PTO!R15C7,PTO!R15C9,PTO!R15C11,PTO!R17C3,PTO!R17C5"

----My Code Example----

For x = 1 to 42
    ' Determine where the 1st day falls
    ' Is it a weekday? Add my coordinates to an array so I can create a named range later.
Next x

CreateNamedRange(weekdayArray)

Please include a screenshot of (a portion) of your workbook to see how your sheet is organized. — pintxo
Not sure I understand your request. When the user presses a button on one sheet, I create a new sheet and then starting at A3, I put a number representing a date for the month I am on. So, if the 1st day of the month starts on a Friday, I move over 6 columns and put a one and then a 2 on the next column (Saturday). Then on the next row down, A4 (Sunday), I put a 3 and so on. Does that help? — Wannabe

Limak Limak · Accepted Answer · 2017-01-16T15:47:56

If you want to assign selected range into variable Test, you can use following command:

ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=" & ActiveSheet.Name & "!" & Selection.Address

It is impossible for us to help you with selecting some particular cells based on another cell is selcted, without further information about structure of your workbook.

Dynamically selecting cells in Excel Worksheet using VBA

1 Answers