VBA脚本新建工作表后无法写入内容问题排查与解决请求

Hey there, I see exactly what's causing your issue—let's break it down and fix it right away!

The Root Problem #

Look at your code flow after creating the new sheet:

Sheets.Add
ActiveSheet.Name = "Stats " & release
Exit Sub ' This line kills the script immediately!

That Exit Sub is the culprit. When you first run the script, it creates the new sheet, names it, then stops entirely before reaching the code that writes headers and merges cells.

On the second run, since the sheet already exists, the On Error GoTo MyError triggers, skips the Exit Sub, and runs the writing code inside the With Worksheets("Stats " & release) block. That's why it works the second time but not the first.

The Fixed Code #

Here's the revised script with key improvements to ensure the writing code runs every time, whether we create a new sheet or use an existing one:

Sub AddColumn()
    'Declare variables
    Dim lastUsedRow As String
    Dim release As String
    Dim statsSheet As Worksheet ' Add variable to reference our stats sheet
    
    'Get the number of current release
    release = ActiveSheet.Name
    
    'Get the number of the current Sheet
    lastUsedRow = CStr(last_Used_Row())
    
    'Write the column header
    With ActiveSheet
        .Range("G1").Value = "Position"
        .Range("H1").Value = "Requested"
        .Range("I1").Value = "Planned"
        .Range("J1").Value = "Delivered"
        .Range("K1").Value = "Tested"
        .Range("L1").Value = "Validated"
        
        'Formula for POSITION
        .Range("G2:G" & lastUsedRow).Formula = "=LOOKUP(A:A,'Raw Data'!B:B,'Raw Data'!D:D)"
        'Formula for REQUESTED
        .Range("H2:H" & lastUsedRow).FormulaR1C1 = "=IF(ISBLANK(R[0]C[-4]), ""NO"", ""YES"")"
        'Formula for PLANNED
        .Range("I2:I" & lastUsedRow).FormulaR1C1 = "=IF(ISBLANK(R[0]C[-4]), ""NO"", ""YES"")"
        'Formula for DELIVERED
        .Range("J2:J" & lastUsedRow).FormulaR1C1 = "=IF(ISBLANK(R[0]C[-4]), ""NO"", ""YES"")"
        'Formula for TESTED
        .Range("K2:K" & lastUsedRow).FormulaR1C1 = _
            "=IF(OR(R[0]C[-1]=""NO"",AND(R[0]C[-1]=""YES"",OR(R[0]C[-4]=""40-To be tested"", R[0]C[-4]=""41-Pending retest"",R[0]C[-4]=""30-Fixed""))),""NO"",""YES"")"
        'Formula for VALIDATED
        .Range("L2:L" & lastUsedRow).FormulaR1C1 = "=IF(AND(R[0]C[-2]=""YES"",R[0]C[-4]=""99-Closed""),""YES"",""NO"")"
    End With
    
    'Create or reference the stats sheet
    On Error Resume Next ' Temporarily ignore errors
    Set statsSheet = ThisWorkbook.Worksheets("Stats " & release)
    On Error GoTo 0 ' Reset error handling
    
    If statsSheet Is Nothing Then
        ' Sheet doesn't exist—create it
        Set statsSheet = ThisWorkbook.Sheets.Add
        statsSheet.Name = "Stats " & release
    Else
        ' Sheet already exists—inform user
        MsgBox "There is already a sheet called ""Stats " & release & """. Updating its content."
    End If
    
    'Write on the sheet the row and column headers
    With statsSheet
        .Range("B7").Value = "S1-Blocking"
        .Range("B8").Value = "S2-Major"
        .Range("B9").Value = "S3-Medium"
        .Range("B10").Value = "S4-Minor"
        .Range("B11").Value = "Total"
        .Range("J7").Value = "S1-Blocking"
        .Range("J8").Value = "S2-Major"
        .Range("J9").Value = "S3-Medium"
        .Range("J10").Value = "S4-Minor"
        .Range("J11").Value = "Total"
        .Range("B13").Value = "S1-Blocking"
        .Range("B14").Value = "S2-Major"
        .Range("B15").Value = "S3-Medium"
        .Range("B16").Value = "S4-Minor"
        .Range("B17").Value = "Total"
        .Range("J13").Value = "S1-Blocking"
        .Range("J14").Value = "S2-Major"
        .Range("J15").Value = "S3-Medium"
        .Range("J16").Value = "S4-Minor"
        .Range("J17").Value = "Total"
        .Range("C6").Value = "Requested"
        .Range("D6").Value = "Planned"
        .Range("E6").Value = "Delivered"
        .Range("F6").Value = "Tested"
        .Range("G6").Value = "Working"
        .Range("K6").Value = "Requested"
        .Range("L6").Value = "Planned"
        .Range("M6").Value = "Delivered"
        .Range("N6").Value = "Tested"
        .Range("O6").Value = "Working"
        
        'Merge cells where needed
        .Range("A7:A11").Merge
        .Range("A13:A17").Merge
        '.Range("A13:A17").Value = "Other"
        .Range("I7:I11").Merge
        .Range("I13:I17").Merge
        '.Range("I13:I17").Value = "Other"
    End With
End Sub

Key Improvements Explained #

1. Removed the premature Exit Sub: Now the writing code runs every time, right after we create or find the stats sheet.
2. Used a worksheet variable (statsSheet): This avoids relying on ActiveSheet, which can be unpredictable if the user clicks elsewhere while the script runs.
3. Safer sheet existence check: Instead of relying on error handling to jump to code, we explicitly check if the sheet exists. This makes the code flow clearer and easier to debug.
4. Wrapped original sheet operations in With ActiveSheet: This ensures all column/header changes apply to the correct sheet (the active one when you run the script) and makes the code cleaner.
5. Clearer user message: When the sheet already exists, the message tells the user exactly which sheet name is duplicated.

Now your script will write content to the stats sheet on the first run, and update the existing sheet on subsequent runs with a friendly message!

内容的提问来源于stack exchange，提问作者Joris