Make this macro open a workbook?

[bpflyr]

I have this macro (was given to me by you wizards!):

Sub GetData()
Application.ScreenUpdating = False
Windows("BDataEntry.xls").Activate
Sheets("Data Entry").Select
Range("A3:AF20").Copy
Windows("BLogbook.xls").Activate
Sheets("Log Entry").Select
Cells(Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
With Application
.ScreenUpdating = True
.CutCopyMode = False
End With
End Sub


Is there a way that it can open BDataEntry automatically. Right now it appears that if BDataEntry is not already open and I try to run the macro, I get a "subscript out of range" error message. If BDataEntry is open all works great. It would be cool though if the macro would open it for me. Is there a way?

 

[acw]

Hi

You could build your own function to see if the workbook is open and action accordingly

Function IsOpen(wb)
  IsOpen = False
  For Each ce In Workbooks
    If ce.Name = wb Then
     IsOpen = True
    End If
 Next ce
End Function

Then change

Windows("BDataEntry.xls").Activate

to

if isopen("BDataEntry.xls") then
Windows("BDataEntry.xls").Activate
else
workbooks.open("c:\temp\BDataEntry.xls")
end if

Change the path name as required.


HTH

Tony

 

[bpflyr]

That looks like it will work, but where do I put the Function?

 

[acw]

Hi

Just put it in the same module as the existing code.


Tony