macro for multiple worksheets
Thanks for the response Dave.
I'll mull it over.
Gord
On Tue, 22 May 2007 07:22:06 -0500, Dave Peterson
wrote:
I'd look for the codenames and then build an array names based on the codename.
(Saved from a previous post)
Option Explicit
Sub testme02()
Dim myCodeNames As Variant
Dim iCtr As Long
Dim mySheetNames() As String
Dim mySht As Object
Dim res As Variant
With ActiveWorkbook
myCodeNames = Array("Sheet1", "sheet3", "Sheet5")
ReDim mySheetNames(1 To .Sheets.Count)
iCtr = 0
For Each mySht In .Sheets
res = Application.Match(mySht.CodeName, myCodeNames, 0)
If IsError(res) Then
'no match, the board goes back
Else
iCtr = iCtr + 1
mySheetNames(iCtr) = mySht.Name
End If
Next mySht
If iCtr = 0 Then
MsgBox "no sheets to copy!"
Else
If UBound(myCodeNames) - LBound(myCodeNames) + 1 < iCtr Then
MsgBox "Not all sheets found"
Else
ReDim Preserve mySheetNames(1 To iCtr)
.Sheets(mySheetNames).Copy 'to a new workbook?
End If
End If
End With
End Sub
Gord Dibben wrote:
JE
On this topic of Worksheets(Array
What is the syntax for an Array of sheets not by hard-coded name but by
codename?
I know Worksheets(Array(1, 2, 4, 7) allows for renaming the sheets but if the
sheets get re-ordered, the 1,2,4,7 is out of whack.
Gord
On Mon, 21 May 2007 14:04:51 -0600, JE McGimpsey wrote:
Number of worksheets selected (only one can be active at a time):
Dim nSelectedWS As Long
nSelectedWS = ActiveWindow.SelectedSheets.Count
for multiple worksheets:
Dim ws As Worksheet
For Each ws In Worksheets(Array("wks1", "wks3", "wks5"))
'do something with each ws
Next ws
In article ,
Brian wrote:
Is there a way to have a macro span multiple worksheets but not the whole
workbook (i.e wks1,wks3,wks5 only)?
Also, is there a way to determine whether multiple worksheets have been
selected and activated?
|