How do I apply VBA code to all worksheets in a workbook?
This code should remove lines containing specific text and apply it to all sheets in the workbook.
When I try this code, it only applies to one sheet.
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
Try the below code, use the instruction With ws
to check all the lines in the corresponding 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
It doesn't work because you never qualify the worksheet you want to work with in your code. A simple fix, keeping your code mostly 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 using the Index Worksheet property with the variable n
you created.
You must specify the sheet in your range operations, otherwise it will work with the ActiveSheet on every 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
Try using this. This is pretty much the same as Shai's answer, but I am activating each leaf. (The next sheet is not automatically activated in "For everyone"; you have to activate it manually each step)
Dim ws As Worksheet
'Walk through each worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
'Add your code here
Next ws