check for existing worksheet in all open excel files

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Thanks Tim. I tried the code you posted and got a syntax error for

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

Any clue why?
 
An unfortunate line break:

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

(notice the spacebar, underscore in the line above)
 
Back
Top