File does not close

I have attached two files. The first file is the "Consolidated" which requires spreadsheets for another file "Append". Once I've added my worksheets from "Append" the "Append file does not always close.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
It does always close but the sheets that are copied contain a "ReurnMenu" macro which during the append process gets set to '<your path>\Append.xlsm'!ReturnMenu so when you go to a sheet and click that button, Excel reloads the Append.xlsm workbook. Change the Append macro to this and that won't happen any more. (Lines 32 and 33 were added).

Sub Append()

    Dim strFileSelected As String
    Dim objOfficeDialog As Object
    Dim wbDestination As Workbook
    Dim wbSource As Workbook
    Dim sh As Worksheet
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set objOfficeDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set wbDestination = ActiveWorkbook
    With objOfficeDialog
        .Title = "Select the Project Cost Allocation file"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        strFileSelected = .SelectedItems(1)
    End With
Dim strSuffix As String
If strFileSelected <> "" Then
        strSuffix = InputBox("Please enter text to append to tab names")
        Set wbSource = Workbooks.Open(strFileSelected)
        For Each sh In wbSource.Sheets
            sh.Copy After:=wbDestination.Worksheets(wbDestination.Worksheets.Count)
            wbDestination.Worksheets(wbDestination.Worksheets.Count).Name = sh.Name & " " & strSuffix
            ActiveSheet.Shapes.Range(Array("Button 1")).Select
            Selection.OnAction = "ReturnMenu"
        Next sh
       ' wbSource.Close False
         wbSource.Close True
    End If
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:


Between lines 33 and 34.
Frank FreeseAuthor Commented:
I was wondering why I was having that problem....thank you
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Frank FreeseAuthor Commented:
I appreciate that very much
Thank you
Frank FreeseAuthor Commented:
ha! ha! caught you...missed End Sub. Well that's OK
Martin LissOlder than dirtCommented:
Here's a freebie. Currently the user is asked to input a four-digit year (which should be validated)  and then later they are asked to "enter text to append to tab names". If the second one should always be the two-digit year of the first on then that second inputbox can be eliminated if you do this.

At the top of modAddYear add Public gstrYear As String

In the AddYear macro add

    gstrYear = Right$(Yearly, 2)


the strYr = " " & Right$(Yearly, 2) line

And finally in the Append macro change

strSuffix = InputBox("Please enter text to append to tab names")


strSuffix = gstrYear
Martin LissOlder than dirtCommented:
In addition to the previous freebie, here's validation for the four-digit year if you want to use it.

After the Yearly = InputBox("Please enter a 4 digit year to append to the added rows") line add

    If Yearly < "2014" Or Yearly > "2050" Then
        MsgBox "Invalid Year please try again"
        Exit Sub
    End If

Open in new window

That only allows years 2014 to 2050 inclusive.
Frank FreeseAuthor Commented:
So you're not hanging around till 2050 either? thanks
Frank FreeseAuthor Commented:
everything changed
I'm glad when this project is done...I want to get back to my Statistics
Martin LissOlder than dirtCommented:
When the user wants to append a file will the file always be the current year? Or perhaps the current year or next year? In other words say it's now 2015. What really are the years that should be allowed to be entered?
Frank FreeseAuthor Commented:
Each workbook represents a project for any given year between 2014 - 2050. It will be up to them to be careful not to overwrite a year they've already done. They're not going back in time, so I've been told. If they do then I'll explain to them how to change the date range. I'm not going forward with appending additional tabs and yearly data to the Consolidated workbook. They think all you've got to do is copy and paste. My response has been "Then copy and paste" and look to someone else to automate whatever else you want.

I'm waiting for final approval of what has been delivered. When I get that then I'll send the final file that has the changes you brought forward on closing the Append file as well as the date routine. The I am done.

What I see happening is that someone will get a hold of these workbooks soon promising things only to making it worse. Just don't call me.
Martin LissOlder than dirtCommented:
Understood but the way the validation is now an input of 2014 would be OK in any current year up to 2050 so they could "go back in time". If you change this

If Yearly < "2014" Or Yearly > "2050" Then


If Yearly < Year(Now) Or Yearly > Year(Now) + 2 Then

they would only be able to add the current year to the current year plus 2, no matter what the current year is. Of course you could widen or narrow the range by changing the "+2".

You could also check to see if any year entered already exists in the workbook and if so don't allow it, but that would be the subject of a new question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.