macros: how to check if a worksheet is present?

[unstuck]

Hi,
I am writing my first "complicated" macro and have a question.
The macro will copy information from specific sheets and place them into a new workbook.
the problem is that thre are multiple workbooks with multiple worksheets. The worksheets are named consistently (plate 1, plate 2 etc), but not all workbooks have the same number of worksheets. Also, there are other worksheets in the workbooks which are not neeed, and threfore the worksheets of interest are not always in the same order (but at least the naming is consistent).

I am trying to write a macro that says
If worksheet named "plate1" is present
then select worksheet "plate1" copy data
else end the sub

followed by another if/then statement saying:
if worksheet named "plate2" is present
then select worksheet"plate2", copy data
else end of sub.

I am having trouble with the syntax checking whether a worksheet is present in the workbook.

I think I am on the right track. I'd appreciate any pointers.
thanks

here's the code if you'd like to see it:

Sub get_data2()
'
' get_data2 Macro
' Macro recorded 9/16/2002 by unstuck
'
' Keyboard Shortcut: Ctrl+m
'
ActiveWorkbook.Activate
Set myWorkbook = ActiveWorkbook
Worksheets("Cover Sheet").Activate
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
myWorkbook.Activate
Sheets("plate 1 analy").Select
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
myWorkbook.Activate
Range("A17:B25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(9, -2).Range("A1").Select

myWorkbook.Activate

If Sheets("plate 2 analy").exists = True Then

Sheets("plate 2 analy").Select
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
myWorkbook.Activate
Range("A17:B25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(9, -2).Range("A1").Select
Else: Exit Sub
End If

myWorkbook.Activate
If Sheets("plate 3 analy").Select = True Then

Sheets("plate 3 analy").Select
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("Exp. 1.xls").Activate
Range("A17:B25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(9, -2).Range("A1").Select
Else: Exit Sub
End If

End Sub

 

[dk]

One way you could do this is to use a function like this:-

<pre>
Function WorksheetExists(WorksheetName As String) As Boolean
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name = WorksheetName Then WorksheetExists = True: Exit For
Next sht
End Function
</pre>

You'd then use this function like this:-

<pre>
Sub test()
If WorksheetExists("Plate 1") = True Then
MsgBox "Plate 1 exists"
Else
MsgBox "Plate 1 doesn't exist"
End If
End Sub
</pre>

 

[unstuck]

thanks!
wher do I insert the function?
It does not work as part of the sub!

 

[dk]

Anywhere outside of the sub. You're basically calling the function from your code.

 

[unstuck]

thank you! I figured it out (just stick the function at teh end, after teh end sub).
It works, Now I'll try and go understand it

Here's the full new code. If anyone has anyother comments/suggestions I'd appreciate hearing it:

Sub get_data2()
'
' get_data2 Macro
' Macro recorded 9/16/2002 by unstuck
'
' Keyboard Shortcut: Ctrl+m
'

ActiveWorkbook.Activate
Set myWorkbook = ActiveWorkbook
Worksheets("Cover Sheet").Activate
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
myWorkbook.Activate
Sheets("plate 1 analy").Select
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
myWorkbook.Activate
Range("A17:B25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(9, -2).Range("A1").Select

myWorkbook.Activate

If WorksheetExists("plate 2 analy") = True Then
Sheets("plate 2 analy").Select
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
myWorkbook.Activate
Range("A17:B25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(9, -2).Range("A1").Select
Else: End If


myWorkbook.Activate
If WorksheetExists("plate 3 analy") = True Then
Sheets("plate 3 analy").Select
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("Exp. 1.xls").Activate
Range("A17:B25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("assemble.xls").Activate
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(9, -2).Range("A1").Select
Else: End If

End Sub

Function WorksheetExists(WorksheetName As String) As Boolean
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name = WorksheetName Then WorksheetExists = True: Exit For
Next sht
End Function

 

[Juan Pablo González]

On 2002-09-18 11:41, dk wrote:
One way you could do this is to use a function like this:-

<pre>
Function WorksheetExists(WorksheetName As String) As Boolean
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name = WorksheetName Then WorksheetExists = True: Exit For
Next sht
End Function
</pre>

You'd then use this function like this:-

<pre>
Sub test()
If WorksheetExists("Plate 1") = True Then
MsgBox "Plate 1 exists"
Else
MsgBox "Plate 1 doesn't exist"
End If
End Sub
</pre>

Dan, you could improve that function a bit, just by using the sht object and error handling, like this:

<pre>
Function WorksheetExists(WorksheetName As String) As Boolean
Dim sht As Object
On Error Resume Next
Set sht = ActiveWorkbook.Sheets(WorksheetName)
WorksheetExists = (Err.Number = 0)
End Function
</pre>Avoiding the loop makes it much more efficient !

 

[unstuck]

alright guys,
would it be easy to improve this macro so that instead of opening each workbook manually and executing the macro, the macro would automatically open all workbooks in a folder, execute (copy out the information), close the workbook and go to the next one, etc.

This seemed too tough for me, but since you solved my problem so easily.....

(Q. what happens when you give a mouse a cookie?
A. He'll ask for a glass of milk!)