从其他工作表提取值无预期结果（空白）的VBA问题排查

问题背景 #

• 需求：从工作簿内名为「dataset」的工作表提取数据，自动填充循环复制的模板表单
• 问题：使用测试字符串（如"TEST"）作为占位符时，能正常覆盖新复制工作表的单元格；但提取「dataset」工作表的值时，新表对应单元格变为空白，调整代码位置后问题仍未解决
• 疑问：是否需要在脚本中重新激活「dataset」工作表？或需改用lookup/index函数？

原VBA代码 #

Sub dataExtraction()

Dim user_state As Integer
Dim index As Integer
Dim nums As Integer
Dim i As Integer
Dim x As Integer

Dim status As String
Dim billingMethod As String

Dim val_1 As String

status = "inaktiv"
billingMethod = "Projektverrechnung"
index = 0

With ThisWorkbook.Worksheets("dataset")

    For i = 1 To 500
            
            index = index + 1
            val_1 = Cells(i, 4).Value
            
        If .Cells(i, 13).Value = status Then

            ThisWorkbook.Sheets("TEMPLATE").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            
            'Cells(4, 3) = "TEST" == WORKING (but only test-value)
            'Cells(4, 3) = Cells(i, 4).Value == NOT WORKING, empty cell
            'Cells(4, 3) = val_1 == NOT WORKING, empty cell
        
            If index < 10 Then
            
            ActiveSheet.Name = "00" & index
            
            Else: ActiveSheet.Name = "0" & index

            End If
                    
        End If
    Next i
End With

MsgBox (index)

End Sub

问题根源 #

你的代码存在两个关键的单元格引用错误：

1. 在With块内，val_1 = Cells(i, 4).Value没有加前缀.，导致Cells默认指向当前激活的工作表，而非「dataset」表，所以val_1实际没拿到正确数据
2. 复制模板后，ActiveSheet切换为新复制的工作表，此时直接用Cells(4,3)赋值，会尝试给新表的单元格赋值，但你之前没正确获取「dataset」的数据，所以显示空白

修复后的代码 #

Sub dataExtraction()

Dim index As Integer
Dim i As Integer
Dim status As String
Dim val_1 As String

status = "inaktiv"
index = 0

With ThisWorkbook.Worksheets("dataset")
    For i = 1 To 500
        ' 只在符合条件时递增索引，避免空计数
        If .Cells(i, 13).Value = status Then
            index = index + 1
            ' 明确引用dataset表的单元格
            val_1 = .Cells(i, 4).Value
            
            ' 复制模板并获取新工作表对象，避免依赖ActiveSheet
            Dim newSheet As Worksheet
            Set newSheet = ThisWorkbook.Sheets("TEMPLATE").Copy(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            
            ' 给新表单元格赋值
            newSheet.Cells(4, 3).Value = val_1
            
            ' 重命名新工作表
            If index < 10 Then
                newSheet.Name = "00" & index
            ElseIf index < 100 Then
                newSheet.Name = "0" & index
            Else
                newSheet.Name = CStr(index)
            End If
        End If
    Next i
End With

MsgBox index

End Sub

对疑问的解答 #

1. 不需要重新激活「dataset」工作表：通过With块配合.前缀，就能稳定引用「dataset」表的单元格，无需切换激活状态，反而能避免因激活状态变化导致的错误
2. 不需要改用lookup/index函数：当前问题是VBA代码的引用错误导致的，修复引用后直接提取数据的效率远高于工作表函数，完全没必要替换

关键注意事项 #

• 永远明确指定单元格所属的工作表对象，避免依赖ActiveSheet或Selection，这是VBA操作工作表时最常见的坑
• 复制模板后，直接将新工作表赋值给变量，后续操作都通过变量完成，不用依赖ActiveSheet的状态

内容的提问来源于stack exchange，提问作者cheese steak jimmy's