'ThisWorkbook' Macro Question

[Dan R.]

I have a macro that I want to run on all sheets except the first one.
Can I just put all the code in 'ThisWorkbook' and somehow tell it to
exclude the first sheet?

Thanks,
Dan

 

[Don Guillett]

Depends on what it is and how you want it to fire. More info?

 

[Bob Phillips]

For Each sh In Thisworkbook.Worksheets
If sh.Index <> 1 Then 'or
If sh.name <> "First Sheet" Then
'your code
End If
Next sh

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[John Bundy]

Using the sheet index you can, this excludes the first page and provides a
sample.
For i = 2 To Worksheets.Count
Sheets(i).Cells(1, 1) = "hi"
Next

 

[Dan R.]

Alright you guys are really going to have to humor me here because I'm
new to this... But by using Bob's example here's what I came up with:
(don't laugh)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim R As Integer
R = 3
For Each sh In Thisworkbook.Worksheets
If sh.name <> "SUMMARY" Then
Do Until R = Cells(Rows.Count, 9).End(xlUp).Row
Select Case Cells(R, 9)
Case "X"
Cells(R, 10) = "Data1"
Cells(R, 11) = "1"
Case "Y"
Cells(R, 10) = "Data2"
Cells(R, 11) = "2"
Case "Z"
Cells(R, 10) = "Data3"
Cells(R, 11) = "3"
End Select
R = R + 1
Loop
End If
Next sh
End Sub

Basically I'm trying to populate values into Sheets 2-5 columns 10 and
11 by using the criteria specified in the Case statements and the code
above isnt working (for obvious reasons I'm sure).

Thanks,
-- Dan

 

[Bob Phillips]

You need to properly qualify the objects

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim R As Long
For Each sh In Thisworkbook.Worksheets
If sh.name <> "SUMMARY" Then
For R = 3 To sh.Cells(sh.Rows.Count, 9).End(xlUp).Row
Select Case sh.Cells(R, 9)
Case "X"
sh.Cells(R, 10) = "Data1"
sh.Cells(R, 11) = "1"
Case "Y"
sh.Cells(R, 10) = "Data2"
sh.Cells(R, 11) = "2"
Case "Z"
sh.Cells(R, 10) = "Data3"
sh.Cells(R, 11) = "3"
End Select
R = R + 1
Loop
End If
Next sh
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dan R.]

Bob I'm getting a compile error 'Loop without Do' when I run this
code... I'm sorry but can you help me again please?

Thanks,
-- Dan

 

[Dave Peterson]

I think Bob changed horses in mid-stream:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim R As Long
For Each sh In Thisworkbook.Worksheets
If sh.name <> "SUMMARY" Then
For R = 3 To sh.Cells(sh.Rows.Count, 9).End(xlUp).Row
Select Case sh.Cells(R, 9)
Case "X"
sh.Cells(R, 10) = "Data1"
sh.Cells(R, 11) = "1"
Case "Y"
sh.Cells(R, 10) = "Data2"
sh.Cells(R, 11) = "2"
Case "Z"
sh.Cells(R, 10) = "Data3"
sh.Cells(R, 11) = "3"
End Select
Next R
End If
Next sh
End Sub

 

[Dan R.]

Perfect, thanks a lot Dave.

-- Dan

I think Bob changed horses in mid-stream:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim R As Long
For Each sh In Thisworkbook.Worksheets
If sh.name <> "SUMMARY" Then
For R = 3 To sh.Cells(sh.Rows.Count, 9).End(xlUp).Row
Select Case sh.Cells(R, 9)
Case "X"
sh.Cells(R, 10) = "Data1"
sh.Cells(R, 11) = "1"
Case "Y"
sh.Cells(R, 10) = "Data2"
sh.Cells(R, 11) = "2"
Case "Z"
sh.Cells(R, 10) = "Data3"
sh.Cells(R, 11) = "3"
End Select
Next R
End If
Next sh
End Sub

 

[Gord Dibben]

Dan

Make these alterations. Might be what you need.

R = R + 1
Next R
End If
Next sh


Gord Dibben MS Excel MVP