宏无法正确恢复工作表格式及重新保护的问题咨询

嘿，我来帮你搞定这个宏的小问题！你遇到的两个异常其实是VBA操作工作表保护时很容易踩的坑，咱们一步步拆解解决：

问题1：行/列格式未恢复 #

刷新数据透视表时，Excel经常会自动调整行高、列宽来适配透视表内容，这就是格式丢失的核心原因。解决办法很直接——提前保存当前的行高和列宽，刷新后再还原。

问题2：工作表保护异常（显示保护但无需密码取消） #

这个问题大概率是你重新保护工作表时，没有正确传递密码参数，或者保护方法的参数设置有误。比如如果只调用ws.Protect而不指定Password，Excel会显示工作表已保护，但实际上没有设置密码，自然可以直接取消。

修正后的完整宏代码 #

把下面的代码替换你原来的宏，记得修改标注的参数：

Sub RefreshPivotAndProtect()
    Dim targetSheet As Worksheet
    Dim pivotTable As PivotTable
    Dim savedRowHeights As Variant
    Dim savedColWidths As Variant
    Dim i As Long
    Dim protectPass As String
    
    ' 替换成你的工作表保护密码
    protectPass = "YourSecurePassword"
    ' 替换成你的目标工作表名称
    Set targetSheet = ThisWorkbook.Worksheets("YourPivotSheet")
    
    ' 第一步：保存当前行高和列宽
    ReDim savedRowHeights(1 To targetSheet.UsedRange.Rows.Count)
    ReDim savedColWidths(1 To targetSheet.UsedRange.Columns.Count)
    
    ' 记录行高
    For i = 1 To targetSheet.UsedRange.Rows.Count
        savedRowHeights(i) = targetSheet.Rows(i + targetSheet.UsedRange.Row - 1).RowHeight
    Next i
    
    ' 记录列宽
    For i = 1 To targetSheet.UsedRange.Columns.Count
        savedColWidths(i) = targetSheet.Columns(i + targetSheet.UsedRange.Column - 1).ColumnWidth
    Next i
    
    ' 第二步：取消工作表保护（如果已保护）
    If targetSheet.ProtectContents Then
        targetSheet.Unprotect Password:=protectPass
    End If
    
    ' 第三步：刷新所有数据透视表
    For Each pivotTable In targetSheet.PivotTables
        pivotTable.RefreshTable
    Next pivotTable
    
    ' 第四步：恢复行高和列宽
    For i = 1 To targetSheet.UsedRange.Rows.Count
        targetSheet.Rows(i + targetSheet.UsedRange.Row - 1).RowHeight = savedRowHeights(i)
    Next i
    
    For i = 1 To targetSheet.UsedRange.Columns.Count
        targetSheet.Columns(i + targetSheet.UsedRange.Column - 1).ColumnWidth = savedColWidths(i)
    Next i
    
    ' 第五步：正确重新保护工作表
    ' 这里可以根据需求调整允许的操作（比如允许筛选、排序等）
    targetSheet.Protect _
        Password:=protectPass, _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        UserInterfaceOnly:=False ' 如果需要宏后续能操作保护的表，可改为True，但重启Excel后需重新运行宏
    
    MsgBox "操作完成！透视表已刷新，工作表已正常保护。"
End Sub

关键细节说明 #

1. 格式恢复：通过UsedRange获取工作表的有效数据区域，提前记录每一行的行高和每一列的列宽，刷新透视表后逐一还原，完美保留原格式。
2. 保护修复：明确传递Password参数，同时设置Contents:=True确保单元格内容被保护。如果你的宏后续需要在保护状态下操作工作表，可以把UserInterfaceOnly设为True——但注意这个设置在Excel关闭后会失效，下次打开文件需要重新运行宏来激活。

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