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
source to share
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
source to share
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.
source to share
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
source to share
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
source to share