Do..Loop in multi sheets

Hi all,
I am writing a do loop vba, it is fine for applying one sheet. Now I want to write vba to automate the do loop program for several sheets. How can I do it?

The following program is only appling to one sheet:
Sub Sub_total()
Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

I tried the follwoing code, but it failed.

Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
With sh
.Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End With
Next sh
End Sub

norika

Add the following line
sh.Select
after line 2 in your second sub.

It should be

Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh
.Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End With
Next sh
End Sub



Mangesh

Thank you. It works.
I have a minor question. In my case, vba applies for all sheets in active workbook. Is it possible to exclude some sheets not applying vba?

TIA

norika

Yes. Try the following if statement:

Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
if sh.Name <> "Sheet1" then ' Sheet1 is then excluded
sh.Select
With sh
.Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End With
end if
Next sh
End Sub


Use or to include more sheets


Mangesh

Thank you very much.
Where do I add 'or' ? I try to add 'or' in line 3
if sh.Name <> "Sheet1" or "Sheet9" then .... but it failed.

TIA
norika

Logical statements have to be output fully i.e.

sh.Name <> "Sheet1" or sh.Name <> "Sheet9"

Hi Norika,

in your case you'll have to use an AND

if ((sh.Name <> "Sheet1") AND (sh.Name <> "Sheet9")) then

you could add more ANDs

Mangesh

Thank you very much. I learn much more.

norika