user11732489
user11732489

Reputation:

Applying Same Excel-VBA Code in All Active Sheets

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

Answers (1)

BigBen
BigBen

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

Related Questions