时间戳

首页 > 解决方案 > Excel For Each ws 不会进入下一个 ws

vba - Excel For Each ws 不会进入下一个 ws

问题描述

背景——我正在使用一个工作簿，其中包含一大堆工作表和大量受到公式影响的数据。我想用这段代码完成几个步骤：

我能够在一个工作表上成功完成这些任务，但它不会继续 - 它试图继续在同一个工作表上一遍又一遍地重复相同的操作。

Sub Macro1
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        'Have also tried "For Each ws In ThisWorkbook.Worksheets at other users suggestions.
        ws.Cells.Activate
        ws.Cells.EntireColumn.Hidden = False
        ws.Cells.EntireRow.Hidden = False
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
        ws.Cells.Activate
        ws.Cells.Copy
        ws.Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        'I put these in here thinking that it might be related to the copying/pasting
        'action that was prohibiting it from changing sheets.

        Application.CutCopyMode = False
        ws.Cells(1, 1).Select  
    Next ws

End Sub

非常感谢任何帮助！！！近一个星期以来，我一直在拔头发（字面意思）试图解决这个问题……我快绝望了！谢谢！

标签： vbaexcelloops

解决方案

尝试以下操作：

Sub Macro1
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Cells.EntireColumn.Hidden = False
        ws.Cells.EntireRow.Hidden = False
        If ws.FilterMode Then ws.ShowAllData
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub

推荐阅读