Reopen Error in Excel Macro

[petes]

Hi Friends!!

I have this code which copy's the data from "Name.xlsx" to "Master.xslx"
I run this macro from Master.xslx file...

Sub mast()

If Dir("C:\test\Name.xlsx") <> "" Then
Workbooks.Open "C:\test\Name.xlsx"
Worksheets("Emp").Select
ActiveSheet.Cells.Select
Selection.Copy
Windows("Master").Activate
Worksheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial
Else
MsgBox "File doesn't exist."
End If
End Sub

I need to alter this code so that, in case if the file "Name.xlsx" is already open, it should automatically save and close...

But right now, the error is dsipalying as

----Name.xlsx is already open. Reopening will cause any changes you made to be discarded . Do you want to reopen Name.xlsx------

Basically i am trying to get rid off this error by automatically saving the opened file before it gets closed.

Thanks Pals!!!

 

[VoG]

Try

Sub mast()

If Dir("C:\test\Name.xlsx") <> "" Then
If Not BookOpen("Name.xlsx") Then Workbooks.Open "C:\test\Name.xlsx"
Worksheets("Emp").Select
ActiveSheet.Cells.Select
Selection.Copy
Windows("Master").Activate
Worksheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial
Else
MsgBox "File doesn't exist."
End If
End Sub
Function BookOpen(wbName As String) As Boolean
On Error Resume Next
BookOpen = Len(Workbooks(wbName).Name)
End Function

 

[petes]

Thanks for the reply... This revised code is working fine except with one error.. If i am executing this Macro from "Master.xlsx" file, i receive the following error

---Runtime error 9, subscript out of range---

I believe this is something to do with the "Windows("Master").Activate" line....

Appreciate your help regarding this....!!

 

[VoG]

Try

Workbooks("Master.xlsx").Activate

 

[petes]

humm...Still the error appears. Now the error is "Subscript out of range"..

not sure what to do...??

 

[VoG]

Are you sure it is master.xlsx, not master.xlsm.

 

[petes]

Yes, the file name is Master.xlsx... sure

 

[VoG]

Are you saying that your original code didn't error on that line? When you get the error click Debug and check which line of code is highlighted.

 

[petes]

If i run in debug mode...(pressing F8) the error is coming after this line
Worksheets("Emp").Select
That means, this line gets highlited and after that if i press F8 the error pops up "Subscrpit out of range"

 

[VoG]

That means that you don't have a worksheet called "emp" - maybe it is " emp" or "emp ".