Run Macro on every worksheet

[blindlemonray]

Hi,

I am trying to run a bit of code on all worksheets in a work book but can only seem to get it to run on the work sheet that I am on. I thought that something like

For Each ws In ActiveWorkbook.Worksheets
    If Range("A58").Value = "Auditor :" Then
       Rows("58:95").Select
       Selection.Insert Shift:=xlDown
    End If
     Next ws
End Sub

would work but it just keeps looping through the code on this current worksheet.

Is there anyway to run code across all worksheets?

Thanks in advance

 

[Rofeu]

Hi,

you need to reference the worksheet in the code:

For Each ws In ActiveWorkbook.Sheets
    If ws.Range("A58").Value = "Auditor :" Then
       ws.Rows("58:95").Insert Shift:=xlDown
    End If
Next

Cheers,
Roel

 

[blindlemonray]

Thanks Rofeu,

have found what I was looking for. This seems to do the job

For Each ws In ActiveWorkbook.Worksheets
ActiveSheet.Next.Select
    If Range("A58").Value = "Auditor :" Then
       Rows("58:95").Select
       Selection.Insert Shift:=xlDown
    End If
     Next ws
End Sub

Many Thanks

 

[Rofeu]

I would aviod using anything that selects or activates. In almost all cases, what you are trying to accomplish can be done without.

It adds redundant lines that make your code less readable, it is more error-prone and slows done your code.

Did you try the code I suggested?

Cheers,

Roel

 

[ClulessChris]

Rofeu, gives good advice here.
the code he provided should work just fine.

Everybody is somebodys Nutter.

 

[SkipVought]

I echo Roel's suggestion to avoid the Activate and Select methods. His origina code will work for you.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[firefytr]

Besides what has been duly noted already, check out this code which is BAD ...

For Each ws In ActiveWorkbook.Worksheets
ActiveSheet.Next.Select
    If Range("A58").Value = "Auditor :" Then
       Rows("58:95").Select
       Selection.Insert Shift:=xlDown
    End If
     Next ws
End Sub

This will fail eventually. Well, not fail, but bomb with bad results. Note the "Range("A58").Value" line? That will be run on the ActiveSheet, not the sheet being referenced. This is an assumption on VBAs part as you did not specify its parent. Instead, use "ws.Range("A58").Value" instead. See the difference? It's HUGE!

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci