VBA workbook open debug

[csilabgirl]

Excel 2002

I have some VBA code in a workbook (workbook #1) that runs when the workbook is opened. The code subsequently opens another workbook (workbook #2) and then copies data from it and pastes it into workbook #1. The problem is sometimes the user already has workbook #2 open when they open workbook #1, so they end up getting a debug error. Is there some different code to work around this? Below is my code. Thanks for the help.

Private Sub Workbook_Open()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim wbOpen As Workbook<o></o>
<o></o>
Set wbOpen = Workbooks.Open(Filename:="\\Server\Chromosomal Labs\ChromoLIMS\Client List.xls")<o></o>
<o></o>
wbOpen.Sheets("Clients").Columns("D:L").Copy<o></o>
ThisWorkbook.Sheets("Clients").Columns("D:L").PasteSpecial Paste:=xlPasteValues
<o> </o>
wbOpen.Close<o></o>
Sheets("Intro").Select
End Sub<o></o>

 

[csilabgirl]

VoG,

Yeah! That worked, thank you so much for your help!

 

[csilabgirl]

VoG,

I am not sure how it worked once and now does not, but I am getting that same compile error and its highlighting the ISOpen part again. Any ideas?

Thanks

 

[bjurney]

Try my code, its something that I use in one of my workbooks and havent had a problem with it yet.

 

[csilabgirl]

Bjurny,

I will give yours a try and let you know.

VoG,
I just realized that in the copy I was testing I had that public code in the module and then also the new this workbook code and it worked. In the final copy I only pasted in the new this workbook code and that it the one that is not working, so I guess I need both codes.

 

[csilabgirl]

Bjurney,

I was reading your code and realize it is set to close workbook #2 (so that is can open it again), but I dont want workbook 2 to close on the user. Thank you for the code thought. It might have an application for something down the road, just not this project.