PDA

View Full Version : selecting all sheets w/ a condition (macro)



gdh05
10-27-2005, 10:51 AM
I have a macro to create different sheets, but an unknown number everytime. I then need to select all the sheets except 2 (always the same 2 - DB and SalaryCalc). I tried doing Select all Sheets, but then they are grouped and I can't just get out of DB and SalaryCalc.

Does anyone know how to select all sheets except those 2 specific ones. Like I said, there will be a different number of sheets everytime...

Thanks for your help.

Bob Phillips
10-27-2005, 11:27 AM
Dim sh As Worksheet
Dim ary
Dim i As Long

ReDim ary(1 To 1)
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "DB" And sh.Name <> "SalaryCalc" Then
i = i + 1
ReDim Preserve ary(1 To i)
ary(i) = sh.Name
End If
Next sh
Worksheets(ary).Select

Norie
10-27-2005, 11:28 AM
Why do you need to select the sheets?

What do you want to do with the sheets?

malik641
10-27-2005, 11:39 AM
Dim sh As Worksheet
Dim ary
Dim i As Long

ReDim ary(1 To 1)
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "DB" And sh.Name <> "SalaryCalc" Then
i = i + 1
ReDim Preserve ary(1 To i)
ary(i) = sh.Name
End If
Next sh
Worksheets(ary).Select
Hey xld, what does that "Preserve" piece of the ReDim mean?

gdh05
10-27-2005, 11:40 AM
Thanks xld, I will try that.

Norie - Here's my original post:

http://vbaexpress.com/forum/showthread.php?t=5796

I haven't received a response for my 2nd response, so I was trying to find ways around it. I was going to select all the dept # sheets and do formatting and pasting of the template that it should really have been moved in to begin with. Any ideas?

thanks!

gdh05
10-27-2005, 11:45 AM
xld, that works, but it's infinite. any way to stop it?

thanks

vonpookie
10-27-2005, 12:22 PM
The code posted by xld is *not* infinite, unless you changed something. Or, you simply have a LOT of worksheets in the workbook, and it seems as if it is not stopping...

The code will stop when it has checked all of the sheets in the workbook (For Each sh In ActiveWorkbook.Worksheets). Once it has done that, the loop stops and it will select all of the sheets--except for the 2 you specified.

mdmackillop
10-27-2005, 02:28 PM
Hey xld, what does that "Preserve" piece of the ReDim mean?

Hi Joseph,
When you redim an array you change its size (as you no doubt know) The Preserve saves the contents of the array within the new array, otherwise the previously assigned values are lost.
Regards
MD

malik641
10-28-2005, 09:59 AM
Hi Joseph,
When you redim an array you change its size (as you no doubt know) The Preserve saves the contents of the array within the new array, otherwise the previously assigned values are lost.
Regards
MDThanks Joanne :thumb Just what I was looking for.

mdmackillop
10-28-2005, 12:09 PM
Hi Joseph.
I'm MD. Joanne is the good looking one who originated my quote.

malik641
10-28-2005, 12:13 PM
Hi Joseph.
I'm MD. Joanne is the good looking one who originated my quote.hahahaha oops http://vbaexpress.com/forum/images/smilies/037.gif my bad, sorry MD...should've checked your profile http://vbaexpress.com/forum/images/smilies/doh.gif

At least I won't forget now http://vbaexpress.com/forum/images/smilies/045.gif

Shazam
10-28-2005, 07:17 PM
Here is another code that you may to use.



Sub PrintExclude()
Application.ScreenUpdating = False
Dim ws
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then
ws.Activate
ActiveSheet.PrintOut
End If
Next ws
Application.ScreenUpdating = True
End Sub