Excel VBA transfer worksheets to a new workbook


I am using Excel 2016 and I want to create a VBA script to do the following:

1.) Refresh all data
2.) Copy specific tabs in the worksheet I have to another worksheet (all of the tabs finalized in 1 worksheet)
3.) Give the final file a specific name.

The names of the tabs in Current Workbook (which has many tabs but I am only focused on the ones below) that I want to move to the final worksheet are:

Tab 1: "Use Override"
Tab 2: "Total Override"
Tab 3: "MMS"
Tab 4: "Users"

and I want to copy them to a new workbook exactly as they are named above with a tab for each with the tab names the same (4 tabs in total for the final workbook):

Tab 1: "Use Override"
Tab 2: "Total Override"
Tab 3: "MMS"
Tab 4: "Users"

The name of the final file:

Ovr_TH Reports_YYMM (The Month would be the previous month of the current month).  So if I ran it now the final file name would be "Ovr_TH Reports_1803"

Please let me know if you need any more info and I greatly appreciate your support.

Michael GrahamData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

NorieAnalyst Assistant Commented:
Perhaps something like this which will save the new workbook in the same folder as the original workbook.
Sub CreateReportWorkbook()
Dim wbNew As Workbook
Dim strPath As String
Dim strFileName As String

    Application.Calculate      ' refresh workbook

    With ThisWorkbook
        .Sheets(Array("Use Override", "Total Override", "MMS", "Users")).Copy
        strPath = .Path
    End With

    strFileName = "Ovr_TH Reports_" & Format(DateSerial(Year(Date), Month(Date), 0), "YYMM") & ".xlsx"

    Set wbNew = ActiveWorkbook

    ' save new workbook in same folder as original
    wbNew.SaveAs strPath & Application.PathSeparator & strFileName, xlOpenXMLWorkbook
    wbNew.Close ' optional
End Sub

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
Michael GrahamData AnalystAuthor Commented:
Wow - this is awesome.  Thank you!!

Is there any way to turn off the formatting in the new file? I am doing this out of Power Query for Excel and it has the the generic filter and some colors.
Michael GrahamData AnalystAuthor Commented:
Thank you for your big help!

I am going to open a new question about the formatting issue.

Thank you again,

NorieAnalyst Assistant Commented:

What formatting do you want to 'lose'?

Is the data on the sheets to be copied in table format after being returned from Power Query?

It would be possible to do a copy/paste special values but then you might lose number formatting.
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

From novice to tech pro — start learning today.