check for existing worksheet in all open excel files

[Guest]

Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any
open excel file. I would also like my macro to copy this worksheet if it
isn't in the current workbook that the macro was run from. This is the code
I found in the newsgroups so far which is a start. Thanks in advance!

Public Function WSExist(wsname As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Object
On Error Resume Next
WSExist = False
Set objWorksheet = ActiveWorkbook.Sheets(wsname)
If Err = 0 Then WSExist = True
End Function


Sub TestMatrix_Chk()

If WSExist("Test Matrix") = False Then
MsgBox ("No Test Matrix sheet found!")
End If

End Sub

 

[Tim Barlow]

Seth,

Try:

Sub findTestMatrix()
Dim wb As Workbook
Dim wSht As Worksheet

For Each wb In Workbooks
Debug.Print "Searching: "; wb.Name
If wb.Name <> ThisWorkbook.Name Then
For Each wSht In wb.Worksheets
Debug.Print "Found: "; wSht.Name
If wSht.Name = "TestMatrix" Then
wSht.Cells.Copy
Destination:=ThisWorkbook.Sheets(1).Range("A1")
End If
Next wSht
End If
Next wb

Set wb = Nothing
Set wSht = Nothing

End Sub


The 'debug.print' are just there so you can see what's going on if you step
through it. You may want to change the 'Destination' sheet.

HTH

Tim

 

[Ardus Petus]

Public Function WSExist(wsname As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Worksheet
On Error Resume Next
Set objWorksheet = ActiveWorkbook.Worksheets(wsname)
On Error GoTo 0
WSExists = Not objWorksheet Is Nothing
End Function


Sub TestMatrix_Chk()

If WSExist("Test Matrix") = False Then
MsgBox ("No Test Matrix sheet found!")
End If

End Sub

HTH

 

[Guest]

Thanks Tim. I tried the code you posted and got a syntax error for

Destination:=ThisWorkbook.Sheets(1).Range("A1")

Any clue why?

 

[Dave Peterson]

An unfortunate line break:

wSht.Cells.Copy _
Destination:=ThisWorkbook.Sheets(1).Range("A1")

(notice the spacebar, underscore in the line above)