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.

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" wrote in message
...
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,

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 Dont Update which
stops the script. Is there a way to tell it Dont Update automatically?

"Bernie Deitrick" wrote:

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" wrote in message
...
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.

Eli,

Try using

Application.DisplayAlerts = False

just before you open the workbook.

HTH,
Bernie
MS Excel MVP


"Eli" wrote in message
...
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" wrote:

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" wrote in message
...
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.

Eli,

Workbooks.Open "Blkacc223508.xls", True

to auto update the links:

Workbooks.Open "Blkacc223508.xls", False

to not update.

Bernie


"Eli" wrote in message
...
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" wrote:

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" wrote in message
...
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.