Excel VBA宏问题：仅首个工作表执行值粘贴，其余未执行

问题根源 #

你的代码存在两个核心问题：

1. 未明确指定操作对象：Cells或Set rng = Cells默认指向当前活动工作表，循环遍历后续工作表时，活动工作表并未切换，导致仅第一个工作表被执行了粘贴值操作。
2. 逻辑错误（初始代码）：sh.Copy会新建一个包含该工作表的工作簿，此时ActiveWorkbook已切换为新工作簿，但你用With ActiveWorkbook包裹的.SaveAs和.Close实际操作的是原工作簿，完全偏离预期。
3. 潜在风险：直接修改原工作簿的工作表内容（转成值），会永久覆盖原公式，可能破坏原始数据。

推荐解决方案（不修改原工作簿） #

优先采用这个方案，在新生成的工作簿中转换值，避免破坏原文件：

Sub SaveFilesInFolder()
    Dim sh As Worksheet
    Dim newWb As Workbook
    Dim destPath As String
    
    ' 指定保存路径，确保末尾带反斜杠
    destPath = "C:\Location\"
    
    For Each sh In ThisWorkbook.Worksheets
        ' 复制当前工作表到新工作簿
        sh.Copy
        Set newWb = ActiveWorkbook
        
        ' 在新工作簿中将公式转为值
        With newWb.Sheets(1).UsedRange
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
        Application.CutCopyMode = False ' 清除剪贴板状态
        
        ' 保存新工作簿，指定xlsx格式避免兼容问题
        newWb.SaveAs Filename:=destPath & sh.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        newWb.Close SaveChanges:=False ' 已保存，直接关闭
    Next sh
End Sub

修改要点 #

• 针对新工作簿的工作表操作，完全不影响原工作簿的数据
• 用UsedRange替代全选Cells，只处理有数据的区域，提升效率
• 明确指定文件格式，避免因Excel版本导致的格式异常
• 清除剪贴板，避免残留操作干扰后续步骤

备选方案（允许修改原工作簿） #

如果你确实需要先将原工作表的公式转为值，再保存副本，可使用以下代码：

Sub SaveFilesInFolder_ModifyOriginal()
    Dim sh As Worksheet
    Dim destPath As String
    
    destPath = "C:\Location\"
    
    For Each sh In ThisWorkbook.Worksheets
        ' 切换到当前工作表，明确操作对象
        sh.Activate
        With sh.UsedRange
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
        Application.CutCopyMode = False
        
        ' 复制并保存工作表
        sh.Copy
        ActiveWorkbook.SaveAs Filename:=destPath & sh.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
    Next sh
End Sub

注意：此方案会永久修改原工作簿的工作表内容（公式变值），执行前请务必备份原文件。

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