check for existing worksheet in all open excel files
 

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

check for existing worksheet in all open excel files
 

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

check for existing worksheet in all open excel files
 

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
--
AP

"Seth" a écrit dans le message de
...
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

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

check for existing worksheet in all open excel files
 

An unfortunate line break:

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

(notice the spacebar, underscore in the line above)

Seth wrote:

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




--

Dave Peterson