0

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

Improve this question
2
  • 1
    You've got unqualified Range and Columns calls, meaning that the Worksheet isn't specified. Move that first End With all the way to the end and add periods in front of each Range and Columns instance that doesn't have one. Commented Jul 11, 2019 at 3:06
  • @BigBen I cannot seem to understand how to add periods. Could you please show it to me? if its okay Commented Jul 11, 2019 at 3:09

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

what should i put in "Set Rng = Range("B1:B1000")" in order to apply it to all worksheets
Once you've moved that End With, add a period: Set Rng = .Range("B1:B1000").
it worked! how about the part in deleting the blanks: 'For deleting the blanks On Error Resume Next ActiveSheet.Range("B:B").SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0 how should I apply it to all worksheets. Thank you very much! you are very helpful and effective
Change ActiveSheet to wks. That's the last line in the answer :)
It should be pretty easy to do, but probably best to ask it in a new question, since it's a different issue.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.