References to multiple sheets

D

djh

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.
 
J

Jim Thomlinson

Sub TraverseSheets
dim wks as worksheet

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

Tom Ogilvy

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
 
D

djh

Thanks, your first solution worked great.

Tom Ogilvy said:
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
Click to expand...
 
D

djh

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.
 
T

Tom Ogilvy

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
 
J

Jim Thomlinson

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.
 
Top