1 Attachment(s)
que: coding for dynamic calendar; multiple drop down lists outputting to new shee
Hi - please kindly assist - and thank you in advance
version: Microsoft® Excel® for Microsoft 365 MSO (Version 2110 Build 16.0.14527.20234) 64-bit
the below coding runs only for column 1 on the automatically created worksheet (and any subsequent sheets (worksheets result from a named range within a dynamic calendar. – Thus, when i click/change the date or year within a dynamic calendar, data outputs to the auto created sheet labeled as the appropriate year.
The Goal and outcome: output data that is typed into "schedule" worksheet (from BOTH column AH and AI dropdown lists) into subsequent auto created sheets please note- data outputs to a column which is unlabeled -but by default/definition of coding means the top column and row refers to a named range day/date of the year corresponding to the "scheldule" worksheet created. (this means that if I type data into the schedule worksheet into drop downs in column AH and have chosen year 2020, January, 1st, then the data will appear in the 2020 worksheet 1st column which references automatically january 1, 2020. Likewise, if a chose a different year, data for the appropriate day/month will populate on a different sheet-entitled 2019, or 2021. I need the data for both columns AH and AI to appear on each subsequent sheet.
Code:
Option Explicit
Dim YearNm As Long, DayCol As Long, DayRow As Long
Dim SelDate As Date
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DataSheet As Worksheet
If Not Intersect(Target, Range("AH6:AH29")) Is Nothing Then
SelDate = Range("AH5").Value 'Selected Date
YearNm = [ScYear]
'Determine if data worksheet exists
On Error Resume Next
Set DataSheet = ThisWorkbook.Sheets("" & YearNm & "")
On Error GoTo 0
If DataSheet Is Nothing Then
ThisWorkbook.Sheets.Add(After:=Sheets("Schedule")).Name = YearNm
Set DataSheet = ThisWorkbook.Sheets("" & YearNm & "")
Activate
End If
DayRow = Target.Row 'Row
DayCol = SelDate - DateSerial(YearNm, 1, 1) + 1 'Determine Column for Data Sheet
DataSheet.Cells(DayRow, DayCol).Value = Target.Value
End If
End Sub
