如何在工作表页眉/页脚配置VLOOKUP函数并实现批量自动更新

1. 移除辅助单元格，直接在VBA中计算VLOOKUP结果 #

你之前依赖I2单元格作为中转的方式会增加冗余，且数据更新后需要手动触发代码刷新。可以直接在VBA内执行VLOOKUP计算，替代辅助单元格：

Sub SetLeftFooterWithVLookup()
    Dim lookupValue As Variant
    Dim result As Variant
    
    ' 获取查找值（对应原公式的$G$5）
    lookupValue = ActiveSheet.Range("G5").Value
    
    ' 执行VLOOKUP并处理匹配失败的情况
    On Error Resume Next
    result = WorksheetFunction.VLookup(lookupValue, ThisWorkbook.Sheets("Product_Details").Range("C7:L100"), 9, False)
    On Error GoTo 0
    
    ' 无匹配时设为空字符串
    If IsEmpty(result) Then result = ""
    
    ' 设置左页脚的格式与内容
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Times New Roman""&11Employee's Name & Signature: &U" & result & "&U"
    End With
End Sub

2. 批量更新所有工作表的页脚 #

要一次性将设置应用到工作簿内所有工作表，只需遍历所有工作表并执行相同逻辑：

Sub SetAllSheetsFooterWithVLookup()
    Dim ws As Worksheet
    Dim lookupValue As Variant
    Dim result As Variant
    
    For Each ws In ThisWorkbook.Sheets
        ' 跳过Product_Details工作表（如果无需为其设置页脚）
        If ws.Name <> "Product_Details" Then
            lookupValue = ws.Range("G5").Value
            
            On Error Resume Next
            result = WorksheetFunction.VLookup(lookupValue, ThisWorkbook.Sheets("Product_Details").Range("C7:L100"), 9, False)
            On Error GoTo 0
            
            If IsEmpty(result) Then result = ""
            
            With ws.PageSetup
                .LeftFooter = "&""Times New Roman""&11Employee's Name & Signature: &U" & result & "&U"
            End With
        End If
    Next ws
End Sub

3. 实现页脚自动更新 #

如果希望数据变化时自动刷新页脚，可通过工作表/工作簿事件实现：

单个工作表自动更新（当G5修改时） #

右键目标工作表标签→选择「查看代码」，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅当G5单元格被修改时触发更新
    If Not Intersect(Target, Me.Range("G5")) Is Nothing Then
        Dim lookupValue As Variant
        Dim result As Variant
        
        lookupValue = Me.Range("G5").Value
        
        On Error Resume Next
        result = WorksheetFunction.VLookup(lookupValue, ThisWorkbook.Sheets("Product_Details").Range("C7:L100"), 9, False)
        On Error GoTo 0
        
        If IsEmpty(result) Then result = ""
        
        With Me.PageSetup
            .LeftFooter = "&""Times New Roman""&11Employee's Name & Signature: &U" & result & "&U"
        End With
    End If
End Sub

全局自动更新（当Product_Details数据变化时） #

按Alt+F11打开VBA编辑器，双击左侧的ThisWorkbook，粘贴以下代码：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' 当Product_Details的C7:L100区域修改时，更新所有工作表页脚
    If Sh.Name = "Product_Details" And Not Intersect(Target, Sh.Range("C7:L100")) Is Nothing Then
        Dim ws As Worksheet
        Dim lookupValue As Variant
        Dim result As Variant
        
        For Each ws In ThisWorkbook.Sheets
            If ws.Name <> "Product_Details" Then
                lookupValue = ws.Range("G5").Value
                
                On Error Resume Next
                result = WorksheetFunction.VLookup(lookupValue, Sh.Range("C7:L100"), 9, False)
                On Error GoTo 0
                
                If IsEmpty(result) Then result = ""
                
                With ws.PageSetup
                    .LeftFooter = "&""Times New Roman""&11Employee's Name & Signature: &U" & result & "&U"
                End With
            End If
        Next ws
    End If
End Sub

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