VBA运行时错误91：未设置对象变量问题求助

Let's break down what's causing that error and get your macro working smoothly.

Why You're Seeing Runtime Error 91 #

That error pops up because after you create a new worksheet, your ws variable isn't actually pointing to the new sheet—it's still set to Nothing. When you try to run ws.Range("H" & Rows.Count).End(xlUp).Row + 1, VBA can't access a property of an uninitialized object, hence the crash.

The wsRow declaration as Long is fine—your real issue is ensuring ws always references a valid worksheet before you try to use it.

The Fix: Properly Assign the New Worksheet to ws #

When you create a new sheet, use the Set statement to link your ws variable directly to the new worksheet. Here's how to adjust your code, plus a full working example:

Corrected Full Macro #

Sub DistributeRowsToSheets()
    Dim programsWs As Worksheet
    Dim ws As Worksheet
    Dim lastProgramsRow As Long
    Dim currentRow As Long
    Dim targetSheetName As String
    Dim wsRow As Long ' No special initialization needed here
    
    ' Set reference to your main "programs" sheet
    Set programsWs = ThisWorkbook.Worksheets("programs")
    lastProgramsRow = programsWs.Range("H" & programsWs.Rows.Count).End(xlUp).Row
    
    ' Loop through each row in column H (skip header if row 1 is headers)
    For currentRow = 2 To lastProgramsRow
        targetSheetName = Trim(programsWs.Range("H" & currentRow).Value)
        
        ' Skip empty cells in column H
        If targetSheetName <> "" Then
            ' Check if the target sheet exists
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(targetSheetName)
            On Error GoTo 0
            
            ' If sheet doesn't exist, create it and assign to ws
            If ws Is Nothing Then
                ' Add new sheet at the end and link to ws
                Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                ws.Name = targetSheetName
            End If
            
            ' Find the next empty row in the target sheet
            wsRow = ws.Range("H" & ws.Rows.Count).End(xlUp).Row + 1
            ' Copy the current row to the target sheet
            programsWs.Rows(currentRow).Copy Destination:=ws.Rows(wsRow)
            
            ' Reset ws to Nothing for the next iteration
            Set ws = Nothing
        End If
    Next currentRow
End Sub

Key Fixes Explained #

1. Assign ws when creating a new sheet: The line Set ws = ThisWorkbook.Sheets.Add(...) ensures ws always points to a valid worksheet before you try to access its range.
2. Safely check for existing sheets: Using On Error Resume Next and On Error GoTo 0 is a clean way to test if a worksheet exists without crashing.
3. Reset ws after each loop: Setting ws = Nothing prevents leftover references from causing issues in the next iteration.

Notes on wsRow #

You don't need to initialize wsRow with a specific value upfront—since you're recalculating it for each target sheet every time, the line wsRow = ws.Range(...) will overwrite any initial value anyway. Declaring it as Long is all you need.

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