批量生成无宏无公式的活动工作表独立工作簿及行高自动调整需求

需求梳理 #

• 仅复制指定的「Template」工作表，生成无宏（.xlsx格式）且去除公式的独立工作簿
• 所有生成的工作簿保存至主文件所在的同一路径
• 对工作表指定区域自动调整行高（Autofit）

修改后的VBA代码 #

Sub GenerateEmployeeScorecards()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False ' 关闭保存时的提示弹窗
    
    Dim srcWS As Worksheet, templateWS As Worksheet
    Dim lastRow As Long, idCell As Range
    Dim savePath As String, fileName As String
    Dim newWB As Workbook
    
    ' 定义源数据工作表和模板工作表
    Set srcWS = ThisWorkbook.Sheets("ID")
    Set templateWS = ThisWorkbook.Sheets("Template")
    
    ' 获取主文件所在路径
    savePath = ThisWorkbook.Path & "\"
    
    ' 获取ID列表的最后一行
    lastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    ' 遍历每个员工ID
    For Each idCell In srcWS.Range("B3:B" & lastRow)
        ' 跳过空单元格
        If idCell.Value <> "" Then
            ' 更新模板中的员工ID
            templateWS.Range("Q2").Value = idCell.Value
            
            ' 自动调整指定区域的行高（可根据实际需求修改区域范围）
            templateWS.Range("A1:Z100").EntireRow.AutoFit
            
            ' 新建空白工作簿
            Set newWB = Workbooks.Add(xlWBATWorksheet)
            
            ' 将模板工作表的值（不含公式）复制到新工作簿
            templateWS.UsedRange.Copy
            newWB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            newWB.Sheets(1).PasteSpecial Paste:=xlPasteFormats
            
            ' 设置新工作簿的工作表名称
            newWB.Sheets(1).Name = "员工评分卡"
            
            ' 生成文件名并保存为无宏xlsx格式
            fileName = savePath & idCell.Value & ".xlsx"
            newWB.SaveAs Filename:=fileName, FileFormat:=xlOpenXMLWorkbook
            
            ' 关闭新工作簿
            newWB.Close SaveChanges:=False
        End If
    Next idCell
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "批量生成完成！"
End Sub

代码关键说明 #

1. 无宏无公式输出：通过新建空白工作簿，使用PasteSpecial粘贴值和格式，彻底剥离原工作表的公式和宏代码，保存为xlOpenXMLWorkbook（即.xlsx格式）
2. 同路径保存：通过ThisWorkbook.Path获取主文件所在路径，避免硬编码路径导致的错误
3. 自动调整行高：使用EntireRow.AutoFit对指定区域（示例为A1:Z100）自动调整行高，可根据实际需求修改区域范围
4. 优化批量处理：关闭屏幕更新和保存提示，提升处理效率；增加空单元格判断，避免生成无效文件

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