Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

File does not close

Folks,
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.
Consolidated.xlsm
Append.xlsm
0
Frank Freese
Asked:
Frank Freese
  • 6
  • 6
1 Solution
 
Martin LissRetired ProgrammerCommented:
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

0
 
Martin LissRetired ProgrammerCommented:
Add            

Range("B1").Select

Between lines 33 and 34.
0
 
Frank FreeseAuthor Commented:
I was wondering why I was having that problem....thank you
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Frank FreeseAuthor Commented:
I appreciate that very much
Thank you
0
 
Frank FreeseAuthor Commented:
ha! ha! caught you...missed End Sub. Well that's OK
0
 
Martin LissRetired ProgrammerCommented:
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)

after

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

And finally in the Append macro change

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

to

strSuffix = gstrYear
0
 
Martin LissRetired ProgrammerCommented:
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.
0
 
Frank FreeseAuthor Commented:
So you're not hanging around till 2050 either? thanks
0
 
Frank FreeseAuthor Commented:
everything changed
I'm glad when this project is done...I want to get back to my Statistics
0
 
Martin LissRetired ProgrammerCommented:
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?
0
 
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.
0
 
Martin LissRetired ProgrammerCommented:
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

to

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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now