CODEWITHSUNDEEP
excelvba
Rovshan Aliyev
Rovshan Aliyev

Reputation: 51

How to apply VBA code to all worksheets in the workbook

This code should delete rows that contain specific text and apply it to all the sheets in the workbook.

When I try this code, it applies to only one worksheet.

Sub WorksheetLoop()

     Dim c As Integer
     Dim n As Integer
     c = ActiveWorkbook.Worksheets.Count
     For n = 1 To c Step 1
        Last = Cells(Rows.Count, "A").End(xlUp).Row
        For I = Last To 1 Step -1
            If (Cells(I, "A").Value) = "Text" Then
                Cells(I, "A").EntireRow.Delete
            End If
        Next I
     Next n

 End Sub

Upvotes: 3

Views: 24956

Answers (4)

Heather Claxton
Heather Claxton

Reputation: 1109

Try using this. It's almost exactly like Shai's answer, but I activate each worksheet. (The next worksheet doesn't automatically activate in the 'For each'; you have to manually activate it each step )

Dim ws As Worksheet

'Walk through each worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate

'Add your code here

Next ws

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27249

It's not working because you never qualify the worksheet that you want to work with in your code. The simple fix, keeping your code mainly as is is below.

Sub WorksheetLoop()

 Dim c As Integer
 Dim n As Integer
 c = ActiveWorkbook.Worksheets.Count
 For n = 1 To c Step 1
    Last = Worksheets(n).Cells(Rows.Count, "A").End(xlUp).Row
    For I = Last To 1 Step -1
        If (Worksheets(n).Cells(I, "A").Value) = "Text" Then
            Worksheets(n).Cells(I, "A").EntireRow.Delete
        End If
    Next I
 Next n

 End Sub

The above works by using the Worksheet Index property with the n variable you have created.

Upvotes: 7

Xzsh4s575sf75
Xzsh4s575sf75

Reputation: 93

You have to specify sheet in your range operations, otherwise it works with ActiveSheet in each iteration.

Sub WorksheetLoop()

 Dim c As Integer
 Dim n As Integer
 c = ActiveWorkbook.Worksheets.Count
 For n = 1 To c Step 1
    Last = Sheets(n).Cells(Rows.Count, "A").End(xlUp).Row
    For I = Last To 1 Step -1
        If (Sheets(n).Cells(I, "A").Value) = "Text" Then
            Sheets(n).Cells(I, "A").EntireRow.Delete
        End If
    Next I
 Next n

End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

Try the code below, use With ws statement to test all rows in that relevant sheet.

Option Explicit

Sub WorksheetLoop()

Dim i As Long
Dim ws As Worksheet
Dim Last As Long

' loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
    With ws
        Last = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = Last To 1 Step -1
            If (.Cells(i, "A").Value) = "Text" Then
                .Cells(i, "A").EntireRow.Delete
            End If
        Next i
    End With
Next ws

End Sub

Upvotes: 5

Related Questions