excel - How can I get VBA code to recognise a worksheet and copy to it - Stack Overflow

admin管理员组

文章数量:1323200

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?

本文标签： excelHow can I get VBA code to recognise a worksheet and copy to itStack Overflow