VBA工作簿打开事件代码调试求助：当月首日且单元格值大于30时执行指定操作

Let's walk through the issues in your code and fix them step by step:

Key Problems in the Original Code #

• Uninitialized pDate variable: You're using pDate to calculate FirstDay, but you never assign a value to pDate (it defaults to 0/December 30, 1899). This means your FirstDay calculation is completely wrong—it won't reference the current month's first day at all.
• Comparing a Range object directly: When you write LastCounterVal > 30, LastCounterVal is a Range object, not the value inside that cell. VBA can't compare an object to a number correctly here, so this condition will either throw an error or evaluate incorrectly.
• Unnecessary Select/Selection calls: These operations are fragile (they depend on worksheet focus) and inefficient. You can work directly with ranges without selecting them first.

Corrected Full Code #

Private Sub Workbook_Open()
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim LastCounterVal As Range
    Dim FirstDay As Date
    Dim Today As Date
    
    ' Initialize worksheet references
    Set copySheet = Worksheets("Sheet1")
    Set pasteSheet = Worksheets("Counter")
    Set LastCounterVal = pasteSheet.Range("D1") ' Reuse pasteSheet reference
    
    ' Calculate today's date and the first day of the current month
    Today = Date
    FirstDay = DateSerial(Year(Today), Month(Today), 1) ' Use Today instead of uninitialized pDate
    
    ' Fix the condition: check cell value, not the Range object
    If FirstDay = Today And LastCounterVal.Value > 30 Then
        ' Unprotect sheets first
        copySheet.Unprotect
        pasteSheet.Unprotect
        
        ' Copy/paste without Select/Selection
        copySheet.Range("AB2").Copy
        pasteSheet.Range("D1").PasteSpecial Paste:=xlPasteValues, _
                                            Operation:=xlNone, _
                                            SkipBlanks:=False, _
                                            Transpose:=False
        Application.CutCopyMode = False
        
        ' Protect sheets again
        pasteSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        copySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
        ' Hide Counter sheet and navigate to Sheet1 B5
        pasteSheet.Visible = False
        copySheet.Range("B5").Select
    Else
        ' Default navigation if condition isn't met
        copySheet.Range("B5").Select
    End If
End Sub

Breakdown of Fixes #

1. Fixed FirstDay calculation: We now use Today (which is set to the current date) to generate the first day of the current month—no more uninitialized variable issues.
2. Range value comparison: Changed LastCounterVal > 30 to LastCounterVal.Value > 30 to compare the actual numeric value in cell D1, not the Range object itself.
3. Removed Select/Selection: We directly reference ranges like pasteSheet.Range("D1") instead of selecting sheets and cells first, making the code more reliable and faster.
4. Cleaned up worksheet references: Reused the pasteSheet variable instead of calling Worksheets("Counter") multiple times, which makes the code easier to maintain.
5. Reordered visibility toggle: We now hide the Counter sheet after the operation, and only unprotect sheets when needed.

内容的提问来源于stack exchange，提问作者Karl Ferreira Mendes