ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   'ThisWorkbook' Macro Question (https://www.excelbanter.com/excel-programming/381278-thisworkbook-macro-question.html)

Dan R.
'ThisWorkbook' Macro Question
 
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
'ThisWorkbook' Macro Question
 
Depends on what it is and how you want it to fire. More info?

--
Don Guillett
SalesAid Software

"Dan R." wrote in message
oups.com...
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

Bob Phillips
'ThisWorkbook' Macro Question
 
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)


"Dan R." wrote in message
oups.com...
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

John Bundy
'ThisWorkbook' Macro Question
 
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

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Dan R." wrote in message
oups.com...
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

Dan R.
'ThisWorkbook' Macro Question
 
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

John Bundy wrote:
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

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Dan R." wrote in message
oups.com...
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

Bob Phillips
'ThisWorkbook' Macro Question
 
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." wrote in message
oups.com...
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

John Bundy wrote:
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

--
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

"Dan R." wrote in message
oups.com...
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

Dan R.
'ThisWorkbook' Macro Question
 
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

Bob Phillips wrote:
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." wrote in message
oups.com...
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

John Bundy wrote:
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

--
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

"Dan R." wrote in message
oups.com...
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

Dave Peterson
'ThisWorkbook' Macro Question
 
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." wrote:

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

Bob Phillips wrote:
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." wrote in message
oups.com...
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

John Bundy wrote:
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

--
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

"Dan R." wrote in message
oups.com...
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



--

Dave Peterson

Dan R.
'ThisWorkbook' Macro Question
 
Perfect, thanks a lot Dave.

-- Dan

Dave Peterson wrote:
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." wrote:

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

Bob Phillips wrote:
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." wrote in message
oups.com...
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

John Bundy wrote:
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

--
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

"Dan R." wrote in message
oups.com...
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



--

Dave Peterson

Gord Dibben
'ThisWorkbook' Macro Question
 
Dan

Make these alterations. Might be what you need.

R = R + 1
Next R
End If
Next sh


Gord Dibben MS Excel MVP

On 19 Jan 2007 09:46:24 -0800, "Dan R." wrote:

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

Bob Phillips wrote:
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." wrote in message
oups.com...
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

John Bundy wrote:
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

--
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

"Dan R." wrote in message
oups.com...
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


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com