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 ***

Applying Macro to only certain sheets
 

"Darin Kramer" wrote in message
...


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"))

Applying Macro to only certain sheets
 

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 ,
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 ***

Applying Macro to only certain sheets
 

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


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

Applying Macro to only certain sheets
 

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 ***