Trouble Closing File

[zoom38]

My file contains the following code in a standard module:


Public fname As String
Public WB As String
Public BegSched As Date

Sub NewSchedFileMain()

Application.ScreenUpdating = False
Call SaveAs
Workbooks.Open Filename:=fname
Workbooks(fname).Activate
Cells(1, 19).Value = BegSched
Application.ScreenUpdating = True

Workbooks(WB).Close savechanges:=True

End Sub

Sub SaveAs()

Dim EndSched As Date

Dim bs As String
Dim es As String

'Save The Active Workbook In Format "RVSD_Mar 26, 2007-Apr 24, 2007"

WB = ActiveWorkbook.Name

BegSched = Sheets(1).Cells(1, 19).Value
EndSched = Sheets(1).Cells(1, 26).Value

BegSched = BegSched + 28
EndSched = BegSched + 27

bs = Format(BegSched, "mmm dd, yyyy")
es = Format(EndSched, "mmm dd, yyyy")

fname = "RVSD_" & bs & "-" & es & ".xls"

ActiveWorkbook.SaveCopyAs Filename:=fname
MsgBox Prompt:="The New Schedule File Was Saved As """ & fname & """", Title:="New Workbook."

End Sub



My problem is with the line "Workbooks(WB).Close savechanges:=True". If I include this line the file closes but I get the following message "application-defined or object-defined error". If I comment this line out I don't get the error message but of course the file doesn't close like I want it to. Can someone advise what I did wrong.

By the way I do have code(below) in "This Workbook" which I can't figure out if it is affecting the above:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ThisSheet As Worksheet
Dim wksh As Worksheet

Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
For Each wksh In Worksheets
wksh.Activate
Range("a1").Select
ActiveSheet.Protect Password:="pword", DrawingObjects:=False, contents:=True, _
Scenarios:=True, userinterfaceonly:=True
Next wksh
ThisSheet.Activate

If ActiveSheet.Index <> 1 And ActiveSheet.Index <> 2 Then
Sheets(1).Activate
End If

'If SaveAs Is Selected, The Following Code Calls The sub
'In Module9b That Updates The Title In The Title Bar.
If SaveAsUI Then
Application.OnTime Now(), "UpdateCaption"
End If

Application.ScreenUpdating = True
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime Now(), "UpdateCaption"
Application.Calculate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'in case the menu item has already been deleted
Application.CommandBars("Worksheet Menu Bar").Controls("My Tools").Delete 'delete the menu item
End Sub

Public Sub Workbook_WindowActivate(ByVal Wn As Window)
' Insert the Revision Date and Version in the Title Bar
Wn.Caption = ThisWorkbook.Name & " ***** (Revised October 16, 2007 - ver 5.0) *****"
End Sub




Thanks
Gary

Edit by Lucas: Line breaks added to keep code from running off the screen to the right...

[lucas]

Have you tried dimming WB as workbook?

[zoom38]

That gives me an error on the "WB = ActiveWorkbook.Name" line.

[lucas]

I get the following message "application-defined or object-defined error".
That error tells you something isn't defined right or something is wrong there....

it gives you and error now on

"WB = ActiveWorkbook.Name" line.
because you have closed WB

could you explain exactly what you are trying to do please?
It would be great if you could post the workbook along with an explaination....zip it if it's too big...

[zoom38]

When I activate this macro in module 9c, I want it to copy the active workbook, name the new workbook, activate the new workbook and close the old workbook.

zip attached

[Bob Phillips]

It errors because Z1 has an error.

[lucas]

Hi Zoom,
Sub SaveAs
seems to do exactly that...what are you trying to do with the rest of the code after you call savas in the module NewSchedFileMain as that seems to be where you are running into problems.....

[zoom38]

Lucas, the SaveAs sub does fine. My problem is with the line "Workbooks(WB).Close savechanges:=True". I am using this line to close the original file. Without this line both the original file and the copy remain open. I only want the copy with the new name established in the "SaveAs" sub to remain open.


Sub NewSchedFileMain()
Dim WB As String
Application.ScreenUpdating = False
WB = ActiveWorkbook.Name 'set WB to the name of the open file

Call SaveAs 'call sub that creates a new file and the name for that file
Workbooks.Open Filename:=fname 'opens the newly created file
Workbooks(fname).Activate 'activates the new file
Cells(1, 19).Value = BegSched 'sets the value in cell S1 of the new file

Workbooks(WB).Close savechanges:=True 'closes the original file

Application.ScreenUpdating = True
End Sub



XLD, do you me I have a problem in cell Z1?? Why does this only occur with the line "Workbooks(WB).Close savechanges:=True"??

[Bob Phillips]

No I mean that when I tried to run your code it failed because it tried to pick up cell Z1, but there was an error there. As the sheet was protected, I couldn't get to the root of it.

[zoom38]

XLD, if you have the time, password is l221.