MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 <> "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 <> "Name of last sheet" then
Sh.Range("B2").Value = 99
end if
Next
End Sub

Hiya