Looping through various worksheets VBA

[mwhite1998]

Hello,

I am looking to create a loop to run through all the worksheets in my workbook to run some code that clears the contents of "blank cells".

The code works for the active worksheet but doesn't seem to run through the loop to the next one.

Sub WorksheetLoop()
Dim ws As Worksheet

For Each ws In Worksheets

Call RemoveBlanks

Next

End Sub

Sub RemoveBlanks()

columnNumber = 1
Do Until columnNumber > 26
    rowNumber = 2
    
    columnLetter = Chr(columnNumber + 64)
    lastRow = Range(columnLetter & rows.Count).End(xlUp).Row
    Do Until rowNumber > lastRow
        If Range(columnLetter & rowNumber).Value = "" Then
            Range(columnLetter & rowNumber).ClearContents
        End If
        rowNumber = rowNumber + 1
    Loop
    columnNumber = columnNumber + 1
Loop


           
End Sub

Thanks for any help

Matt

 

[FryGirl]

Hi Matt, maybe

change

For Each ws In Worksheets

to

For Each ws In ActiveWorkbook.Worksheets

 

[mwhite1998]

Hi Matt, maybe

change

For Each ws In Worksheets

to

For Each ws In ActiveWorkbook.Worksheets

Nope, unfortunately same problem

 

[FryGirl]

It works for me.

Try this. When I tested like this, every sheet is called.

Sub WorksheetLoop()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Call RemoveBlanks
    Next
End Sub

 

[mwhite1998]

It works for me.

Try this. When I tested like this, every sheet is called.

Sub WorksheetLoop()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Call RemoveBlanks
    Next
End Sub

This one worked! Must be the ws.activate?

Either way, thanks!

 

[FryGirl]

The main reason it was not working on all worksheets was because the code did not know to move from sheet to sheet.

Example:

lastRow = Range(columnLetter & rows.Count).End(xlUp).Row

The code didn't have a way to know that it needs to find the lastrow on any other worksheet than the active one.

The best way to handle this is using the ws.activate or simply building the code within the first code versus calling it. So again, an example. The line above would be

lastRow = ws.Range(columnLetter & rows.Count).End(xlUp).Row