check for existing worksheet in all open excel files
Thanks Tim. I tried the code you posted and got a syntax error for
Destination:=ThisWorkbook.Sheets(1).Range("A1")
Any clue why?
"Tim Barlow" wrote:
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
"Seth" wrote in message
...
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
|