Copy 4 sheets from template and dynamically name, Save template and leave workin file open

**Webbers** · 10-15-2019, 05:05 PM

I have a code that currently works, however it does not complete my task. Okay I have variables on Sheet5 "codes" (I prefer the code names to the sheet names... just in case someone renames them).

sheet5 variables:
C15 - Concatenation of new filename desired (c22 & I18)
C22 - "base" file name
I18 - date added to filename (added in format of mm.dd.yy)

There are a total of 6 sheets in my template (where the macros are). The ones being copied are Sheet1, Sheet3, Sheet4, Sheet5. The desire is to copy those 4 sheets to a new workbook, name the file using the base name and the date, save as an xlsx (no macros) to the same directory that the template is located. I also want to have sheet1 and sheet5 hidden. Leave the NEW file open, save and close the template.

Currently, the macro copies those 4 sheets to a new workbook, however I cannot get the template files to save and close, or the new file to hide those 2 sheets. I am close.

Sub SaveSomeSheets()


Dim FName As String
Dim Path As String

' Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False


' Sheet name and cell listing file name
    FName = Sheet5.Range("$C$22") & " " & Format(Sheet5.Range("$I$18"), "mm.dd.yy") & ".xlsx"
    Path = ActiveWorkbook.Path & "\" & FName


Sheets(Array(Sheet1.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name)).Copy
    ActiveWorkbook.SaveAs Filename:=Path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'    ActiveWindow.Close

ResetSettings:
' Reset Macro Optimization Settings
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True

End Sub

**Greg M** · 10-15-2019, 07:47 PM

Hi there,

however I cannot get the template files to save and close, or the new file to hide those 2 sheets. I am close

You were indeed!

See if the following version of your code does what you need:




Option Explicit


Sub SaveSomeSheets()

    Dim wbkNew  As Workbook
    Dim FName   As String
    Dim Path    As String

'   Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False


'   Sheet name and cell listing file name
    FName = Sheet5.Range("$C$22") & " " & Format(Sheet5.Range("$I$18"), "mm.dd.yy") & ".xlsx"
    Path = ThisWorkbook.Path & "\" & FName

    Sheets(Array(Sheet1.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name)).Copy

    Set wbkNew = ActiveWorkbook

    With wbkNew

        .Worksheets(Sheet1.Name).Visible = xlSheetHidden
        .Worksheets(Sheet5.Name).Visible = xlSheetHidden

        .SaveAs Filename:=Path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    End With

    ThisWorkbook.Close SaveChanges:=True

'   Reset Macro Optimization Settings
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

**Webbers** · 10-16-2019, 09:52 AM

Greg M.

Thanks for the response. It produced an error. Attached are screenshots. sheet1 & sheet5 are hidden, and sheet3 & sheet4 are displayed for the user. I am not sure what happened. Again, we get to the saving the file part when we have the errors.

**Webbers** · 10-17-2019, 10:18 AM

Greg M.

I decided to try something, I moved my template from a development folder (for today) to my download folder... shorter path. I used your last code, and ran it a few times, most of the macro worked just fine. The macro:
1) copied the 4 sheets
2) hid the 2 sheets I wanted it to
3) Renamed the new file based on the dynamic variables to the same path as the template
4) The new file was left open (for the user to work)

The only thing that did not happen is the original file (the template) needs to save and close after everything is done. Can ya help?

**Greg M** · 10-19-2019, 07:38 AM

Hi again,

Thanks for your feedback.

I think including the highlighted line at the very end of your routine should do what you need:


'   Reset Macro Optimization Settings
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True


    ThisWorkbook.Close SaveChanges:=True

Hope this helps,

Regards,

Greg M

**Greg M** · 10-29-2019, 02:39 PM

Hi again,

Many thanks for the Reputation increase - much appreciated!

Glad I was able to help.

Best regards,

Greg M

Copy 4 sheets from template and dynamically name, Save template and leave workin file open

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy 4 sheets from template and dynamically name, Save template and leave workin file open

Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA to copy data to a template file then save it as a new file.

[SOLVED] Open a template, make some changes to cells and then save the file

[SOLVED] looper to copy tabs of a data file into template and save as

Template with Macro won't work once copy of template is made and file name is changed

Creating dynamically sheets with data and template

Why does it take so much longer to open a file based on a template than the template?

[SOLVED] Open a template, copy 3 worksheets w ranges and save as workbook

Bookmarks

Bookmarks

Posting Permissions