Make this macro open a workbook?

bpflyr

Board Regular
Joined
Nov 7, 2005
Messages
116
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?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

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

Code:
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
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

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


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,118,534
Messages
5,572,741
Members
412,482
Latest member
arooshrana2
Top