Same action on all but one worksheets

Posted by JAF on April 20, 2001 8:00 AM

Hiya

I have a workbook containing (currently) 26 worksheets. Each worksheet contains individual cases which are updated each month. The last worksheet in the workbook summarises the individual data.

I need to update all the worksheets to move data in Column C into Column D on all sheets EXCEPT the last sheet.

I have the code for performing the same action on all sheets (as below), but I can't work out how to get it to stop when it gets to the last sheet.

Sub SameAction()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets
Sh.Range("B2").Value = 99
Next
End Sub

To summarise, I want to perform the same action (in the example above putting a value of 99 in cell B2 on all sheets) but I don't want to do anything to the last sheet.

Suggestions...

JAF

Posted by Dave Hawley on April 20, 2001 8:13 AM

Hi JAF

Try this:

Sub SameAction()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets
If Sh.Name &LT;&GT; "Summary" Then
Sh.Range("B2").Value = 99
End If
Next
End Sub

Dave
OzGrid Business Applications

Posted by Dave Hawley on April 20, 2001 8:16 AM

Or.....

If you are confident it will always be that last sheet as by Index number, you could use:

Sub SameAction()
Dim i As Integer
For i = 1 To Sheets.Count - 1
Sheets(i).Range("B2").Value = 99
Next i
End Sub

Dave

OzGrid Business Applications

Posted by Jerid on April 20, 2001 8:21 AM

This is one way to do it.

Sub SameAction()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets
if SH.name &LT;&GT; "Name of last sheet" then
Sh.Range("B2").Value = 99
end if
Next
End Sub

Hiya