ignore if workbookis already open

Vac :
What's up with all the error traps ? I had provided you with a solution which only required one trap in the "CopyRow3" procedure , in my last posting to you ....

Public Sub MainCopyProcedure()
' the variables are : (SourceBk, SourceSht, TargBk, TargSht)
Call CopyRow3("Paul.xls", "dailyfigures ", "Master.xls", "Paul")
Call CopyRow3("Mark.xls", "dailyfigures ", "Master.xls", "Mark")
Call CopyRow3("John.xls", "dailyfigures ", "Master.xls", "John")

End Sub

Private Sub CopyRow3(SourceBk, SourceSht, TargBk, TargSht)
On Error GoTo NotFound
With Workbooks(TargBk).Sheets(TargSht)
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
Workbooks(SourceBk).Sheets(SourceSht).Range("3:3").Copy _
Destination:=.Range(NxRw & ":" & NxRw)
End With
NotFound:
End Sub

PLEASE NOTE: That all your Error Traps have been removed and replaced with the one I had suggested in my previous post.

Public Sub MainCopyProcedure()
If Not fileIsOpen("Master.xls") Then
Workbooks.Open fileName:="C:\Documents and Settings\David Culshaw\My Documents\master.xls"
End If
' the variables are : (SourceBk, SourceSht, TargBk, TargSht)
Call CopyRow3("Paul.xls", "dailyfigures ", "Master.xls", "Paul")
Call CopyRow3("Mark.xls", "dailyfigures ", "Master.xls", "Mark")
Call CopyRow3("John.xls", "dailyfigures ", "Master.xls", "John")

End Sub

Private Sub CopyRow3(SourceBk, SourceSht, TargBk, TargSht)
On Error GoTo NotFound
With Workbooks(TargBk).Sheets(TargSht)
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
Workbooks(SourceBk).Sheets(SourceSht).Range("3:3").Copy _
Destination:=.Range(NxRw & ":" & NxRw)
End With
NotFound:
End Sub



Function fileIsOpen(fileName As String) As Boolean
Dim wrkBook As Workbook
For Each wrkBook In Workbooks
If UCase(wrkBook.Name) = UCase(fileName) Then
fileIsOpen = True
Exit Function
End If
Next wrkBook
fileIsOpen = False
End Function

This version not only checks to see if file exist it also gives alert if can't be found in the expected path.

Public Sub MainCopyProcedure()
On Error GoTo NoMaster
If Not fileIsOpen("Master.xls") Then
Workbooks.Open fileName:="C:\Documents and Settings\David Culshaw\My Documents\master.xls"
End If
' the variables are : (SourceBk, SourceSht, TargBk, TargSht)
Call CopyRow3("Paul.xls", "dailyfigures ", "Master.xls", "Paul")
Call CopyRow3("Mark.xls", "dailyfigures ", "Master.xls", "Mark")
Call CopyRow3("John.xls", "dailyfigures ", "Master.xls", "John")


Exit Sub
NoMaster:
pt = MsgBox("C:\Documents and Settings\David Culshaw\My Documents\master.xls", vbCritical, "No Master File Found:")

End Sub

Private Sub CopyRow3(SourceBk, SourceSht, TargBk, TargSht)
On Error GoTo NotFound
With Workbooks(TargBk).Sheets(TargSht)
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
Workbooks(SourceBk).Sheets(SourceSht).Range("3:3").Copy _
Destination:=.Range(NxRw & ":" & NxRw)
End With
NotFound:
End Sub



Function fileIsOpen(fileName As String) As Boolean
Dim wrkBook As Workbook
For Each wrkBook In Workbooks
If UCase(wrkBook.Name) = UCase(fileName) Then
fileIsOpen = True
Exit Function
End If
Next wrkBook
End Function

That is great Nimrod, Thank You very much. I hadnt put in you error code yet, it still had a line in that I was trying to use. Thanks very much for your quick responce and all your help, vac

That works great, Thanks. What I need to add is that when this procedure is run the source sheet, whichever one it is, is closed. It wont matter if the sheet is saved.
I was trying to use a code similar to this:
ActiveWorkbook.Close False
after each of the source sheets, but it dosnt work if not all the source sheets are open.
Thanks

just insert to your sub copyrow3 below notfound: :

workbooks(SourceBk).close false


In addition, don't put exteionsion .xls when you call copyrow3("yourfile.xls"), it will produce an error message, just yourfile
name will do.

Thanks very much for the reply, unfortunately i cant get the code to work with that ne line in. I have removed the xls extensions, but with the new line in it comes up error message box, cant find master file, even though its there and open. If I remove the new line evreything works ok.
Thanks

Ok thanks to all who helped. By adding this line
On error resume next
Ive got it to work ok.
Thank You