Reputation:
In need of help in applying the following code below for all sheets. I have tried the code I found online which is ApplyToAllSheets() but I am still new and I don't know how I can make it work. Please help.
Sub ApplyToAllSheets()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
Third wks
Next
End Sub
Sub Third(wks As Worksheet)
Dim Rng As Range
Dim cell As Range
Dim ContainWord As String
With wks
Set Rng = .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp))
End With
'For deleting the remaining informations not necessary
Set Rng = Range("B1:B1000")
ContainWord = "-"
For Each cell In Rng.Cells
If cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell
Set Rng = Range("C1:C1000")
ContainWord = "2019" 'change to current year
For Each cell In Rng.Cells
If cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell
Set Rng = Range("A1:A1000")
ContainWord = "-"
For Each cell In Rng.Cells
If cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell
'For deleting the blanks
On Error Resume Next
ActiveSheet.Range("B:B").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
'For shifting the date to the left
Columns("C").Cut
Columns("A").Insert Shift:=xlToLeft
Columns("C").Cut
Columns("B").Insert
'For deleting the negative sign "-"
With Columns("B:B")
.Replace What:="-", Replacement:=""
End With
End Sub
It should successfully apply the code to all the sheets My result is that the first sheet was always cleared and the other sheets are untouched. please help
Upvotes: 0
Views: 67
Reputation: 49998
You've got unqualified - meaning the Worksheet
isn't qualified - Range
and Columns
calls.
This is good - note the period in front of each instance of Range
, as well as before Rows
.
With wks
Set Rng = .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp))
End With
This, not so much:
Set Rng = Range("B1:B1000") ' no worksheet specified, so it's the ActiveSheet, not wks.
Or again:
Columns("C").Cut
Move that first End With
all the way to the end of the Sub
, and add a period in front of each instance of Range
and Columns
. By doing so, they will reference wks
and not imply the ActiveSheet
.
While you're at it, change that instance of ActiveSheet
to wks
. You want to work with wks
, not the ActiveSheet
.
Upvotes: 1