Skipping a tab when with this macro

[Guest]

I am currently using this macro to populate a column with the names of each
tab I have in a workbook. I would like to be able to exclude a tab when this
macro runs. If the tab name is Sheet1, how would I exclude it from showing
up in my column?

For Each ws In Worksheets
i = i + 1
Cells((i + 3), 1) = ws.Name
Next

Thanks for your time!
-Kai

 

[Guest]

You could do this:-
Sub nnn()
For Each ws In Worksheets
i = i + 1
If ws.Name = ("Sheet1") Then
i = i - 1
GoTo 100
End If
Cells((i + 3), 1) = ws.Name
100
Next
End Sub

Mike

 

[Dave Peterson]

For Each ws In Worksheets
if lcase(ws.name) = lcase("sheet1") then
'skip it
else
i = i + 1
Cells(i + 3, 1).value = ws.Name
end if
Next ws

 

[Guest]

simplify:

i = 3
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then
i = i + 1
Cells(i, 1) = ws.Name
End If
Next ws

 

[Guest]

I just realized that I may need to skip more than one sheet when running the
macro. I have tried copying and pasting in the critical parts (if else
statement) with the other worksheet name, but it brings up a "For" error when
it gets to the "Next" statement whenever I try it. Any suggestions on how I
can set it up to be able to add more sheets to the "ignore list?" Thanks
again for the speedy replies and the time!
-Kai

 

[Dave Peterson]

There are lots of different ways:

For Each ws In Worksheets
select case lcase(ws.name)
case is = lcase("sheet1"), lcase("Sheet2"),lcase("sheet 99")
'skip it
case else
i = i + 1
Cells(i + 3, 1).value = ws.Name
end select
Next ws

= = =
If you're careful, you don't need to include the lcase() stuff on this line:

case is = lcase("sheet1"), lcase("Sheet2"),lcase("sheet 99")
just make sure you type in lower case:
case is = "sheet1", "sheet2", "sheet 99"

==========
Another:

dim res as variant
dim NamesToSkip as variant

namestoskip = Array("sheet1","sheet3","sheet999")

For Each ws In Worksheets
res = application.match(ws.name,namestoskip,0)

if iserror(res) then
'skip it
else
i = i + 1
Cells(i + 3, 1).value = ws.Name
end if

Next ws