Closing Data/Template File with Current File..

schafers

Board Regular
Joined
Jul 5, 2005
Messages
74
I'm trying to close a second file that opens just fine with the master file I want, but I can't get it to work. Anyone got any ideas? Code below. Thanks!!

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set bdrwb = ActiveWorkbook
Workbook.Activate "BDR Sales People - Areas & Values.xls"
MsgBox "activated other workbook"

dataWB.Activate
ActiveWorkbook.Close

bdrwb.Activate
template = Right(ActiveWorkbook.Name, 12)

' If template = "TEMPLATE.xls" Then
ActiveWorkbook.Close False
' End If

HideSheets
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
If you just want the workbook closed, and the name is always the same, you can use something like this:

Code:
Private Sub Close_wb()
  Workbooks("workbook.xls").Close
End Sub

Where 'workbook.xls' is the name of the workbook to close. Of course you can always add some error checking in here too just in case the workbook is already closed, etc.
 
Upvote 0
Now Subscript Error.. Any Clues?

changed the code, and got it working, but then saved the file as a new name, and getting subscript error. Any clues?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Workbooks("BDR Sales People - Areas & Values.xls").Close False

ActiveWorkbook.Close False

HideSheets
End Sub

Private Sub Workbook_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ActiveWorkbook.Unprotect "warranty"

For Each sht In ThisWorkbook.Sheets
sht.Unprotect "warranty"
Next sht

Set bdrwb = ActiveWorkbook

Workbooks.Open Filename:="c:\BDR Directory - DO NOT TOUCH - DO NOT RENAME\BDR Sales People - Areas & Values.XLS"

bdrwb.Activate

For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then sht.Protect "warranty"
Next sht

ActiveWorkbook.Protect "warranty"
Application.ScreenUpdating = False
Application.DisplayAlerts = True


UnhideSheets
End Sub
 
Upvote 0
To me it seems you're not being very clear. Your initial post makes it sound as if you are just wanting to close a workbook. I give you the code to do that, then you post more elaborate code wanting to do more.

Question: Do you just want to close a workbook, or do you want to do things before you close?

Question: While closing, are you doing any saving, renaming, or otherwise, or just closing?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top