What is the correct syntax to process through all sheets in a workbook when
you do not know how many sheets there will be? I want to have a macro run to
delete certain rows in each sheet, but the number of sheets will vary.

Sub TraverseSheets
dim wks as worksheet

for each wks in worksheets
msgbox wks.name
wks.range("A5:A10").select
next wks
end sub

--
HTH...

Jim Thomlinson


"djh" wrote:

What is the correct syntax to process through all sheets in a workbook when
you do not know how many sheets there will be? I want to have a macro run to
delete certain rows in each sheet, but the number of sheets will vary.

Dim sh as worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Range("5:8").Delete
Next

another way

Sub SS()
Set sh = ActiveSheet
ActiveWorkbook.Worksheets.Select
Range("5:8").Select
Selection.Delete
sh.Select
End Sub


--
Regards,
Tom Ogilvy


"djh" wrote in message
...
What is the correct syntax to process through all sheets in a workbook
when
you do not know how many sheets there will be? I want to have a macro run
to
delete certain rows in each sheet, but the number of sheets will vary.

Thanks, your first solution worked great.

"Tom Ogilvy" wrote:

Dim sh as worksheet
for each sh in ActiveWorkbook.Worksheets
sh.Range("5:8").Delete
Next

another way

Sub SS()
Set sh = ActiveSheet
ActiveWorkbook.Worksheets.Select
Range("5:8").Select
Selection.Delete
sh.Select
End Sub


--
Regards,
Tom Ogilvy


"djh" wrote in message
...
What is the correct syntax to process through all sheets in a workbook
when
you do not know how many sheets there will be? I want to have a macro run
to
delete certain rows in each sheet, but the number of sheets will vary.

I ran into a probelm with run-time error 1004. Select method of range failed.
I ended up using code from Tom Ogilvy's posting.
Thanks anyway.

"Jim Thomlinson" wrote:

Sub TraverseSheets
dim wks as worksheet

for each wks in worksheets
msgbox wks.name
wks.range("A5:A10").select
next wks
end sub

--
HTH...

Jim Thomlinson


"djh" wrote:

What is the correct syntax to process through all sheets in a workbook when
you do not know how many sheets there will be? I want to have a macro run to
delete certain rows in each sheet, but the number of sheets will vary.

In that code, You have to select the sheet before you select the range on
the sheet.


Sub TraverseSheets
dim wks as worksheet

for each wks in worksheets
wks.Select
msgbox wks.name
wks.range("A5:A10").select
next wks
end sub

--
Regards,
Tom Ogilvy


"djh" wrote in message
...
I ran into a probelm with run-time error 1004. Select method of range
failed.
I ended up using code from Tom Ogilvy's posting.
Thanks anyway.

"Jim Thomlinson" wrote:

Sub TraverseSheets
dim wks as worksheet

for each wks in worksheets
msgbox wks.name
wks.range("A5:A10").select
next wks
end sub

--
HTH...

Jim Thomlinson


"djh" wrote:

What is the correct syntax to process through all sheets in a workbook
when
you do not know how many sheets there will be? I want to have a macro
run to
delete certain rows in each sheet, but the number of sheets will vary.

Thanks for clearing that up Tom. I originally had a clearcontents in there
but changed it at the last second for fear that the OP might run it against
some critical data... Didn't think it through.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

In that code, You have to select the sheet before you select the range on
the sheet.


Sub TraverseSheets
dim wks as worksheet

for each wks in worksheets
wks.Select
msgbox wks.name
wks.range("A5:A10").select
next wks
end sub

--
Regards,
Tom Ogilvy


"djh" wrote in message
...
I ran into a probelm with run-time error 1004. Select method of range
failed.
I ended up using code from Tom Ogilvy's posting.
Thanks anyway.

"Jim Thomlinson" wrote:

Sub TraverseSheets
dim wks as worksheet

for each wks in worksheets
msgbox wks.name
wks.range("A5:A10").select
next wks
end sub

--
HTH...

Jim Thomlinson


"djh" wrote:

What is the correct syntax to process through all sheets in a workbook
when
you do not know how many sheets there will be? I want to have a macro
run to
delete certain rows in each sheet, but the number of sheets will vary.