Opening a Workbook with a Macro

[Eli]

I currently have a Macro that will copy data from another workbook into my
current workbook but I have to have the second one open for this to work. I
would like to change this so I do not have to manually open the second
workbook. I want to be able to press my shortcut key and have the second
workbook open, copy the data, paste it in my current workbook, and close the
second workbook. Here is the Macro that I currently have:

Windows("Blkacc223508.xls").Activate
Worksheets("Total-1").Select
Range("C3:N14").Select
Selection.Copy
Windows("2008 worksheets.xls").Activate
Range("B59").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A72").Select
End Sub

Thanks in advance for your help.

 

[Bernie Deitrick]

Eli,

If you are certain it will be closed, change:

Windows("Blkacc223508.xls").Activate

to

Workbooks.Open "Blkacc223508.xls"

Though you have need to add the path in there as well, such as

Workbooks.Open "C:\Excel Files\Blkacc223508.xls"

HTH,
Bernie
MS Excel MVP

 

[Eli]

Bernie,

Thank you that works perfectly. I have one other small glitch. When the
second workbook opens, it is linked to still another workbook and thus it
puts up a dialog box asking me if I want to Update or Don’t Update which
stops the script. Is there a way to tell it Don’t Update automatically?

 

[Bernie Deitrick]

Eli,

Try using

Application.DisplayAlerts = False

just before you open the workbook.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Eli,

Workbooks.Open "Blkacc223508.xls", True

to auto update the links:

Workbooks.Open "Blkacc223508.xls", False

to not update.

Bernie