Close Workbook #2 Before Close

[CLR]

Hi All..........

I have two workbooks open, the second having been opened automatically when
the first one opened. Workbook1 is where I do my business and Workbook2 is
for reference and calculations. When I want to close Workbook1, I would
like to first have Workbook2 close by "Before close" macro..........I have
attempted it as below. the two middle lines were recorded and work fine
when triggered manually, but not in this instance..............could someone
please show me where I'm going wrong? The macro hangs on the line starting
with "Windows".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Windows("_ACpowerCalculator_022504_RevA10Master.xls").Activate
ActiveWorkbook.Close
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3

 

[mudraker]

CLR

This code worked ok when i tried it

On error commands are only incase the 2nd work book is not open

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("_ACpowerCalculator_022504_RevA10Master.xls").Clos
SaveChanges:=False
On Error GoTo 0
End Su

 

[CLR]

Thank you kind Sir.............it looks great to me............I'll give it
a try tomorrow when I get back to the network..........

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

What do you mean by opens automatically. What causes it to open?

What do you mean by not in this instance? What happens - do you get an
error (what does it say)?

 

[CLR]

Hi Tom...........

The second workbook opens with this:

Private Sub Workbook_Open()
Workbooks.Open Filename:= _
"L:\Working\_ACpowerCalculator_RevB1_031104\_ACpowerCalculator_022504_RevA10
Master.xls"
Range("E23").Select
Windows("_ACpowerCalculator_031104_RevB1.xls").Activate
Range("E23").Select
End Sub

When I go to close the first book with this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Windows("_ACpowerCalculator_022504_RevA10Master.xls").Activate
ActiveWorkbook.Close
End Sub

I get error "Run time error "9" Subscript out of range", and when I press
"Debug" it highlights the line starting with "Windows"

When I say "not in this instance", what I meant was that the two
lines...........

Windows("_ACpowerCalculator_022504_RevA10Master.xls").Activate
ActiveWorkbook.Close

........work fine when I trigger them manually as a stand alone macro, as
they were recorded, but when I put them in the "Before Close" macro they
don't work...........

 

[CLR]

Hi mudraker...........

Thanks, this code seems to close the workbook2 alright for me too, but my
system didn't seem to like the "SaveChanges:=False" line for some
reason...........'twould be nice if that line worked
too............<G>.......any idea why it don't in my system?

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

That is because they should be on a single line

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("_ACpowerCalculator_022504_RevA10Master.xls").Close _
SaveChanges:=False
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy

Hi mudraker...........

Thanks, this code seems to close the workbook2 alright for me too, but my
system didn't seem to like the "SaveChanges:=False" line for some
reason...........'twould be nice if that line worked
too............<G>.......any idea why it don't in my system?

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Mudraker gave you a fix for that - however, if the second workbook were
being opened because you created a reference to it from the first workbook -
it would not fix it. That was the reason for my inquiry. Since it isn't
the case, it isn't a consideration.

 

[CLR]

Thanks Tom..........that works more better............

Vaya con Dios,
Chuck, CABGx3

That is because they should be on a single line

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("_ACpowerCalculator_022504_RevA10Master.xls").Close _
SaveChanges:=False
On Error GoTo 0
End Sub

 

[CLR]

Not a problem Tom...........thanks for your consideration..........your
other suggestion helped me to get there to where it's working fine
now........

Vaya con Dios,
Chuck, CABGx3