Auto Rename originating "opened" file name in the VBA code when "Saved" or "Saved As"

[emack1230]

Good afternoon everyone,

My code has my open workbook set to the template name, Set bk = Workbooks("Hybrid packet.xlsm") 'assumed open.

This template will be resaved before being filled out. Is there a way for the VBA code to auto change this file name to the new file name when saved? The whole process is to copy and paste other tabs from other workbooks into this template.

Thank you in advance.

 

[rlv01]

Yes there are ways.

'How to save a copy of your current Workbook to a new file while keeping original file open.

Sub SaveWB()
'This code is for example purposes, and is provided with the understanding that you know
'enough about VBA to modify it to suit your specifc needs.
'
    Dim Folder As String, FileName As String, FilePath As String, Msg As String
    Dim DestWB As Workbook
    Dim Ans As Integer

    Folder = "C:\Users\MyUser\Documents\MyFolder"     'user edit required - folder must already exist
    FileName = "MyNewWorkbook.xlsm"                   'user edit required

    Folder = Trim(Folder)
    If Not Right(Folder, 1) = "\" Then
        Folder = Folder & "\"                         'code to add backslash if not present
    End If

    With CreateObject("Scripting.FileSystemObject")   'check that the specified folder exitsts
        If Not .FolderExists(Folder) Then
            MsgBox "Specifid folder not found:" & vbCr & Folder, vbOKOnly Or vbExclamation, "File Folder Error"
            Exit Sub
        End If
    End With

    If InStr(FileName, ".") > 0 Then
        FileName = Left(FileName, InStr(FileName, ".") - 1)
    End If

    FilePath = Folder & FileName & ".xlsm"

    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs (FilePath)
    DoEvents                                          'optional
    Set DestWB = Application.Workbooks.Open(FileName:=FilePath)
    DoEvents                                          'optional

    ' Code modifiying new workbook goes here.
    '



    '
    ' end modifiying new workbook.
    'save changes
    Msg = "Save as macro-enabled workbook?" & vbCr & vbCr
    Msg = Msg & "Yes -  Keep Macros (.xlsm)" & vbCr
    Msg = Msg & "No  -  Strip Macros  (.xlsx)"

    Ans = MsgBox(Msg, vbYesNo + vbQuestion + vbDefaultButton1)
    If Ans = vbNo Then                                'save to new file format
        DestWB.SaveAs FileName:=Folder & FileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook    'see XlFileFormat Enumeration for different formats
        DoEvents                                      'optional
        DestWB.Close False
        Kill FilePath
    Else                                              'retain existing format
        DestWB.Close True
    End If
    Application.DisplayAlerts = True
End Sub

 

[emack1230]

Sorry, I don't think my explanation was very understanding. Each plant will have this template to eventually save as their own with their plant name. In the VBA code, in order to copy/paste a worksheet from another workbook, the template name is in the code. But when they do a save as and change the name, I am needing the VBA code to auto change the template name so it still have the path to copy paste from the other worksheet. I have included a flowchart diagram with the code that I hope will help.

 

[rlv01]

Your diagram is increasing confusion instead of adding clarity, mainly because there is no provided correlation between generalized refernces ("new hire workbook", "plant job qualification workbook", etc.) and what I see in your posted code.

Saving a workbook to a new workbook with a different name is a common thing. The principles are the same no matter whether you are tracking new hires or making a list of pizza toppings.

Your posted code does these things.

1. Assigns a variable to an open WB
2. Opens a 2nd workbook
3. Copies a range from the 2nd WB to a worksheet in the 1st WB.
4. Closes the 2nd WB, saving any changes.

Nowhere does it indicate what workbook you want saved/renamed or what the new name is.

I recommend you leave off talking in broad generalities and instead provide some specific information that can be used to code an example.

1. Name of the workbook you want to save to a new name.​

2. The new name.​

referenced to the workbooks you have defined in your posted Sub OpenWorkbook()

*(one thing, please do not post code as a graphic image. It makes it too hard to work with. Instead please post the code using code tags and the instructions below

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[emack1230]

Your diagram is increasing confusion instead of adding clarity, mainly because there is no provided correlation between generalized refernces ("new hire workbook", "plant job qualification workbook", etc.) and what I see in your posted code.

Saving a workbook to a new workbook with a different name is a common thing. The principles are the same no matter whether you are tracking new hires or making a list of pizza toppings.

Your posted code does these things.

1. Assigns a variable to an open WB
2. Opens a 2nd workbook
3. Copies a range from the 2nd WB to a worksheet in the 1st WB.
4. Closes the 2nd WB, saving any changes.

Nowhere does it indicate what workbook you want saved/renamed or what the new name is.

I recommend you leave off talking in broad generalities and instead provide some specific information that can be used to code an example.

1. Name of the workbook you want to save to a new name.​

2. The new name.​

referenced to the workbooks you have defined in your posted Sub OpenWorkbook()

*(one thing, please do not post code as a graphic image. It makes it too hard to work with. Instead please post the code using code tags and the instructions below

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

Thank you for the feedback and apologies for still not getting it clear. I was hoping the flowchart would show how the original workbook incorporated the other 2 workbooks. What is comes down to is, in the code I posted to copy from one and past in the main workbook "Hybrid packet" I know the users, in which this is for, will need to save the "Hybrid packet" as a different name, multiple times since it is used for new hires. If the name is changed before the VBA runs the copy/paste worksheet, then the paste function will not know where to point correct? That is the section I was worried about, to see if there was a way to make the file name change from "Hybrid packet" to what the new one is, automatically.

I have also been reading and experimenting with ThisWorkbook.sheets(""). Thinking this might be the easier way to go instead of worrying about the file name always changing.

Thank you for your time as well, I really appreciate the insight and direction.

 

[rlv01]

I have also been reading and experimenting with ThisWorkbook.sheets(""). Thinking this might be the easier way to go instead of worrying about the file name always changing.

If the Sub OpenWorkbook() is contained in Workbooks("Hybrid packet.xlsm") that would be the (much) better way to go if your users can save that workbook to a new name.

Set bk = Workbooks("Hybrid packet.xlsm")

becomes

Set bk = ThisWorkbook

 

[emack1230]

If the Sub OpenWorkbook() is contained in Workbooks("Hybrid packet.xlsm") that would be the (much) better way to go if your users can save that workbook to a new name.

Set bk = Workbooks("Hybrid packet.xlsm")

becomes

Set bk = ThisWorkbook

A long way around to an easy approach. Again I appreciate your time and patients.