Applying Macro to only certain sheets

Howdie,

My VB currently applies to all sheets in workbook.
I only want it to apply to sheets A, D, E
(Even if I hide sheets b and C) it sitll applies it to them
Can I change the VB below to specify my required sheets as a range, and
then refer to that range somehow...?

VB extract:


For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = Lastrow(DestSh)

sh.Range("b9:p20").Copy DestSh.Cells(Last + 1, "A")

*** Sent via Developersdex http://www.developersdex.com ***

"Darin Kramer" <darin_kramer@hotmail.com> wrote in message
news:Oh36#oHuFHA.1032@TK2MSFTNGP12.phx.gbl...
>
>
> Howdie,
>
> My VB currently applies to all sheets in workbook.
> I only want it to apply to sheets A, D, E
> (Even if I hide sheets b and C) it sitll applies it to them
> Can I change the VB below to specify my required sheets as a range, and
> then refer to that range somehow...?
>
> VB extract:
>
>
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = Lastrow(DestSh)
>
> sh.Range("b9:p20").Copy DestSh.Cells(Last + 1, "A")
>
> *** Sent via Developersdex http://www.developersdex.com ***

For Each Sh In Worksheets(Array("A", "B", "C"))

Thank you so so so so much!!!!!


*** Sent via Developersdex http://www.developersdex.com ***

One way:

Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "[ADE]" Then
'do stuff
End If
Next ws


In article <Oh36#oHuFHA.1032@TK2MSFTNGP12.phx.gbl>,
Darin Kramer <darin_kramer@hotmail.com> wrote:

> Howdie,
>
> My VB currently applies to all sheets in workbook.
> I only want it to apply to sheets A, D, E
> (Even if I hide sheets b and C) it sitll applies it to them
> Can I change the VB below to specify my required sheets as a range, and
> then refer to that range somehow...?
>
> VB extract:
>
>
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = Lastrow(DestSh)
>
> sh.Range("b9:p20").Copy DestSh.Cells(Last + 1, "A")
>
> *** Sent via Developersdex http://www.developersdex.com ***

You can create a collection of worksheets and then traverse the collection
something like this

Public MySheets As Collection

Sub AddSheets()
Set MySheets = New Collection
MySheets.Add Sheet1, Sheet1.Name
MySheets.Add Sheet2, Sheet2.Name

End Sub

Sub Test()
Dim wks As Worksheet

Call AddSheets
For Each wks In MySheets
MsgBox wks.Name
Next wks

End Sub

--
HTH...

Jim Thomlinson


"Darin Kramer" wrote:

>
>
> Howdie,
>
> My VB currently applies to all sheets in workbook.
> I only want it to apply to sheets A, D, E
> (Even if I hide sheets b and C) it sitll applies it to them
> Can I change the VB below to specify my required sheets as a range, and
> then refer to that range somehow...?
>
> VB extract:
>
>
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = Lastrow(DestSh)
>
> sh.Range("b9:p20").Copy DestSh.Cells(Last + 1, "A")
>
> *** Sent via Developersdex http://www.developersdex.com ***
>