macros: how to check if a worksheet is present?

unstuck

New Member
Joined
Sep 17, 2002
Messages
6
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

New Member
Joined
Sep 17, 2002
Messages
6
thanks!
wher do I insert the function?
It does not work as part of the sub!
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Anywhere outside of the sub. You're basically calling the function from your code.
 

unstuck

New Member
Joined
Sep 17, 2002
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

New Member
Joined
Sep 17, 2002
Messages
6
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!)
 

Forum statistics

Threads
1,144,770
Messages
5,726,186
Members
422,661
Latest member
foxleinlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top