Copy the below function and use it in your macro as shown in the below example

Sub Macro1()
If IsWorkbookOpen("book3.xls") Then
'place your code
End If

End Sub
Function IsWorkbookOpen(strWorkbook) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(strWorkbook)
If Not wb Is Nothing Then IsWorkbookOpen = True
End Function


--
Jacob (MVP - Excel)


"Jock" wrote:

Wb1, Sht2 has data in cols A-D. This data is copied to this location by code
daily when new cases are started on sht1 in the same Wb.
Once a week, I need the data from Sht2 to be copied to WB2, Sht3 by clicking
a button. Once data copied over to Wb2, Sht 3, the data on Wb1, Sht 2 to be
deleted.
The problem I am having is checking to see if Wb2 is open - I get the
'subscript out of range' error.
How do I check if a workbook is open (without getting a debug window), open
it if it is closed and show a message to that effect?
--
Traa Dy Liooar

Jock