Selecting Next Worksheet
 

I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

Selecting Next Worksheet
 

Maybe...

Option Explicit
Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.select
'ACTIONS TO BE DONE
Next sht
End Sub

But for the most part, it's not necessary to select the worksheet to work with
it.

If you qualify your ranges (or whatever objects your actions work on), it may
work quicker.

Option Explicit
Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.range("a1").clearcontents
'ACTIONS TO BE DONE
Next sht
End Sub



BSII wrote:

I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

--

Dave Peterson

Selecting Next Worksheet
 

Your code is basically correct, but I suspect your understanding of it is
not. In a For Each loop, each worksheet is NOT activated in the loop. The
Active Sheet remains what it was before the code executes unless you
specifically make it the active sheet. For example, suppose Sheet1 is the
active sheet.

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Range("A1").Value = 123
Next WS

will change A1 on the Sheet1, NOT on each sheet in the loop. Instead, you
should use your control variable to reference the sheet in the loop. E.g.,

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Range("A1").Value = 123
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"BSII" wrote in message
...
I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I
can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

Selecting Next Worksheet
 

post your code. maybe you're not qualifying the ranges.

for example:

this will not work:, it will only enter test into the active sheet:
Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
Range("A1") = "test"
Next
End Sub

this will:

Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "test"
Next
End Sub
--


Gary


"BSII" wrote in message
...
I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

Selecting Next Worksheet
 

Without seeing your "actions to be done code" hard to tell.

This code does the job as advertised.

Sub shtsprotect()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Protect Password:="justme"
Next sht
End Sub


Gord Dibben MS Excel MVP


On Sat, 20 Jan 2007 17:56:02 -0800, BSII wrote:

I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

Selecting Next Worksheet
 

Thanks everyone - my apologies for not being more specific. What I'm really
trying to do is write a subroutine tha that will run a couple of subroutines
on each worksheet in my workbook. The current code is:

Sub Timesheet_Format()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub2.TimeSub2"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub3.TimeSub3"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub4.TimeSub4"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub5.TimeSub5"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub6.TimeSub6"
Next

End Sub

The "TimeSubs" are subroutines that format and arrange my data. From the
other suggestions, it sounds like I'm not using the "For Each" correctly. Is
there a better way to do this?


"Gary Keramidas" wrote:

post your code. maybe you're not qualifying the ranges.

for example:

this will not work:, it will only enter test into the active sheet:
Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
Range("A1") = "test"
Next
End Sub

this will:

Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "test"
Next
End Sub
--


Gary


"BSII" wrote in message
...
I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

Selecting Next Worksheet
 

Your code is fine for looping through the sheets. You just need to add a
command as below:

So assuming your procedures work on the activesheet:

Sub Timesheet_Format()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
sh.Activate
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub2.TimeSub2"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub3.TimeSub3"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub4.TimeSub4"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub5.TimeSub5"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub6.TimeSub6"
Next

End Sub

I would suggest that you don't name your procedures with names that
duplicate names of modules. I have heard that can be problematic. Also,
you can have more than one procedure in a module.


--
Regards,
Tom Ogilvy

"BSII" wrote in message
...
Thanks everyone - my apologies for not being more specific. What I'm
really
trying to do is write a subroutine tha that will run a couple of
subroutines
on each worksheet in my workbook. The current code is:

Sub Timesheet_Format()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub2.TimeSub2"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub3.TimeSub3"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub4.TimeSub4"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub5.TimeSub5"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub6.TimeSub6"
Next

End Sub

The "TimeSubs" are subroutines that format and arrange my data. From the
other suggestions, it sounds like I'm not using the "For Each" correctly.
Is
there a better way to do this?


"Gary Keramidas" wrote:

post your code. maybe you're not qualifying the ranges.

for example:

this will not work:, it will only enter test into the active sheet:
Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
Range("A1") = "test"
Next
End Sub

this will:

Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "test"
Next
End Sub
--


Gary


"BSII" wrote in message
...
I'm looking for some code to perform the same group of operations on
each
worksheet in a workbook. It seems to be a very simple operation, but I
can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...

Selecting Next Worksheet
 

Just a note...

You may want to make it so the module and the subroutine have different names.

PERSONAL.XLS!Mod_TimeSub2.TimeSub2

I've seen bad things happen when both the module and the procedure have the same
name.

BSII wrote:

Thanks everyone - my apologies for not being more specific. What I'm really
trying to do is write a subroutine tha that will run a couple of subroutines
on each worksheet in my workbook. The current code is:

Sub Timesheet_Format()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub2.TimeSub2"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub3.TimeSub3"
Application.Wait Now + TimeValue("00:00:01") 'Delay

ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub4.TimeSub4"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub5.TimeSub5"
ThisWorkbook.Application.Run "PERSONAL.XLS!TimeSub6.TimeSub6"
Next

End Sub

The "TimeSubs" are subroutines that format and arrange my data. From the
other suggestions, it sounds like I'm not using the "For Each" correctly. Is
there a better way to do this?

"Gary Keramidas" wrote:

post your code. maybe you're not qualifying the ranges.

for example:

this will not work:, it will only enter test into the active sheet:
Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
Range("A1") = "test"
Next
End Sub

this will:

Sub Timesheet_Format()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "test"
Next
End Sub
--


Gary


"BSII" wrote in message
...
I'm looking for some code to perform the same group of operations on each
worksheet in a workbook. It seems to be a very simple operation, but I can't
seem to get it to work. I have tried the following:

Sub Timesheet_Format()

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

' ACTIONS TO BE DONE

Next

End Sub

It runs ok for the current worksheet, but doesn't go on to any of the
others. Any suggestions on what I'm doing wrong?

Thanks...




--

Dave Peterson