CODEWITHSUNDEEP
vbaexcelloops
Myth03
Myth03

Reputation: 1

Excel For Each ws not going to next ws

Background -- I am working with a workbook with a whole bunch of sheets and a lot of data that is compromised of formulas. I am wanting to accomplish a few steps with this code:

I am able to successfully complete these tasks on ONE worksheet, but it will not continue on -- it tries to continue repeating the same action on the same worksheet over and over.

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

Greatly appreciate any help!!! I've been pulling hair out of my head (literally) for almost a week trying to troubleshoot this... I'm getting desperate! Thanks!

Upvotes: 0

Views: 471

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

Try the following:

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

Upvotes: 2

Related Questions