Macro Loop through all sheets

I have a macro that currently only runs on one sheet. I have about 50 sheets that I need this to run through in the same workbook. Any ideas on how to make it run all sheets?


This is the macro:


Sub Delete_Columns1()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("C4:AW4”), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "BENCHMARK" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireCOLUMN.Delete
End Sub


As always, thanks for all your help!

Please Login or Register  to view this content.

Thanks for the reply- there is an error with the following

Dim rng As Range, cell As Range, del As Range, ws

compile error: syntax error

Works fine for me. You could try changing it to:

Please Login or Register  to view this content.

I suppose but I doubt it will make any difference.

Sorry, error is with

For Each cell In rng

Do all of your sheets have some data in C4:AW4?

It won't work on an empty sheet for example, maybe try:

Please Login or Register  to view this content.

It worked on the active sheet then gave error


Method "Union of object'_Global' failed.

Not all sheets are identical

Sorry, don't know what the problem is, it will run the same macro on each sheet so I would guess there is something in your data on the other sheets that is causing a problem. Can you upload your workbook then I can take a better look.

Data has been deleted but the headings and format are the same.

Also, there are about 30 more sheets in the actual file that roughly follow the same pattern- I had to delete them because the file was too large.


Thanks.

Sorry, my mistake. the del variable needs clearing on each loop through:

Please Login or Register  to view this content.

Thank you very much for your help!!! It works beautifully!