I have a workbook with several sheets that list tasks associated with aspects of my role at work (e.g. cases, donor, travel, etc.). I have also have a sheet (planning) that lists the tasks I want to do that week and I can assign how much time I want to spend on that for each day of the week.
I would like to amend the workbook so that instead of manually copying tasks from the different sheets to the planning sheet, I can simply double-click a task (or the cell listing the task) in any of the other worksheets, and that task will be copied will be copied to the first available call in the relevant range in the planning sheet.
I was planning to add this code to all of the sheets listing tasks:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim planningSheet As Worksheet
Dim nextEmptyCell As Range
' Set reference to the Planning sheet
On Error Resume Next
Set planningSheet = ThisWorkbook.Sheets("Planning")
On Error GoTo 0
If Not Intersect(Target, Me.Range("B2:B100")) Is Nothing Then ' Check if double-clicked range is within B2:B100
Cancel = True ' Prevent default double-click behavior (edit mode)
Set nextEmptyCell = planningSheet.Range("A21:A35").Find("", LookIn:=xlValues) ' Find the first empty cell in range A21:A35 on the Planning sheet
If Not nextEmptyCell Is Nothing Then
nextEmptyCell.Value = Target.Value
End If
End If
End Sub
That raised the error that the activex components cannot create the object (raised by the line Set planningSheet = ThisWorkbook.Sheets("Planning").
I assumed that was because it did not recognise the worksheet 'Planning', so I added the code:
' Check if the planningSheet is set correctly
If planningSheet Is Nothing Then
MsgBox "The 'Planning' sheet could not be found.", vbCritical
Exit Sub
End If
Indeed the message box appears when I double-click a cell.
I know the name of the sheet is correct (I copied and pasted it from a formula elsewhere in the workbook that works fine in moving from and to the 'Planning' worksheet.
Any idea how I can get the VBA code to recognise the 'Planning' worksheet?