close a "do you want to save box"

[royboy531]

in middle of my macro I open a new book and at the end of the macro I close it but in the macro the pop up box that asks "save before close" comes up. I need a code to auto select no to close the box. anyone have any ideas. thanks

 

[BrianB]

You need to add a line of code to save the workbook with either SaveChanges:=True or SaveChanges:=False.

 

[royboy531]

I still can't seem to get it to work. when I put in SaveChanges:=True or SaveChanges:=False I get a compile error: expected: expression. I tried taking the colon out, didn't work. I also tried puttig save instead of savechanges, didn't work. any other suggestions??? thanks for the help.
[/code]

 

[Norie]

Can you post the code?

You might try this.

Application.DisplayAlerts= False

' your code here

Application.DisplayAlerts= True

By the way where did you put Brian's suggestion?

 

[royboy531]

Norie,
I was putting Brian's code after Activewindow.close. For your suggestion am I supposed to put Brian's code between yours or part of my code?? I am a bit confused????
thanks
royboy531

 

[Norie]

Can you post your code?

 

[royboy531]

here it is Norie

Sub macro6()
UserForm1.Show
End Sub

Private Sub btnFilter_Click()

    Dim startDate As String
    Dim shift As String
    
    'reads date from the userform
    startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date
    shift = tbshift 'tbshift is the name of the textbox for shift
    
    'FILTERS
    With Worksheets("sheet1")
        .AutoFilterMode = False
        .Range("a1:p1").AutoFilter
        .Range("a1:p1").AutoFilter Field:=2, Criteria1:=shift
        .Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
       UserForm1.Hide
        Range("A1:P" & Cells(Rows.Count, "A").End(xlUp).Row).Select
End With
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(6, 7, _
        8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Range("a1").Select
    Range("A1:P" & Cells(Rows.Count, "C").End(xlUp).Row).Select
    With Selection.Font
        .Size = 9
    Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
        :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("A1:P1").Select
    With Selection.Font
    .Size = 8.5
    End With
    With ActiveSheet.PageSetup
        .CenterHeader = "&D  &T"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = ActiveSheet.UsedRange.Address
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWindow.Close
    Application.DisplayAlerts = False
    Savechanges = True
    Application.DisplayAlerts = True
    Range("a1").Select
    Selection.AutoFilter
    Selection.AutoFilter


End Sub

 

[Norie]

This has both Brian and I's suggestions.

Private Sub btnFilter_Click()
Dim startDate As String
Dim shift As String
    
    'reads date from the userform
    startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date
    shift = tbshift 'tbshift is the name of the textbox for shift
    
    Application.DisplayAlerts = False
    
    'FILTERS
    With Worksheets("sheet1")
        .AutoFilterMode = False
        .Range("a1:p1").AutoFilter
        .Range("a1:p1").AutoFilter Field:=2, Criteria1:=shift
        .Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
       UserForm1.Hide
        Range("A1:P" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
    End With
    
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(6, 7, _
        8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    
    With Range("A1:P" & Cells(Rows.Count, "C").End(xlUp).Row)
        Font.Size = 9
        .AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
        :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    With Range("A1:P1").Font
        .Size = 8.5
    End With
    
    With ActiveSheet.PageSetup
        .CenterHeader = "&D  &T"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = ActiveSheet.UsedRange.Address
    End With
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    ActiveWindow.Close Savechanges:=True
    
    Range("A1").AutoFilter
    Range("A1").AutoFilter

    Application.DisplayAlerts = True
    
End Sub

 

[royboy531]

that was it. thanks Norie and BrianB. big help

royboy531