Excel VBA - copy of sheets to new Excel

Dave KIlby
Dave KIlby used Ask the Experts™
on
I have a spreadsheet with VBA to run some reports - that has 5 different workbooks, once it has completed running I want to copy 3 of the sheets to a new Excel and save it to a directory - Is this possible and if so how ?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Commented:
Hi there!

Just to check,  are all the names of the tabs you are trying to copy the same name?

Author

Commented:
i want to copy the 3 of the 5 to a completely new spreadsheet, so the new sheet is completely blank, need to open new  spreadsheet.
NoahHardware Tester and Debugger

Commented:
So the new speadsheet in the new workbook must include all three sheets? In other words, all three sheets are combined into one?
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
not into one sheet, but all 3 sheets copy over to a new spreadsheet and then saved.
NoahHardware Tester and Debugger

Commented:
Okay, I think I get it. You should not refer using a "spreadsheet", what you meant is a new "workbook". Give some time, I will work on it.
Roy CoxGroup Finance Manager

Commented:
Try this, change the sheet names as required

Sub SaveSheets()
'
    Dim NewName As Variant

    Sheets(Array("Sheet1", "Sheet3", "Sheet4")).Copy
    NewName = Application.GetSaveAsFilename
    If NewName <> False Then
        ActiveWorkbook.SaveAs Filename:=NewName, FileFormat:=51
    End If
End Sub

Open in new window

Author

Commented:
The copying of the sheets works and it tries to save the file, but after the new workbook is created the save file dialog box opens with the file name in it.

Is there anyway to automatically save the file, so the save file dialog box doesnt open and to automatically close the new sheet once it has been saved ?

thanks for the help
Roy CoxGroup Finance Manager

Commented:
I'm not really sure what you mean. I've just run the code and the only time the save dialog opens is to allow the user to provide a name and directory for the new workbook.
Roy CoxGroup Finance Manager

Commented:
Do you want to just save as the current name, perhaps with a version number

Author

Commented:
i want to save it in a certain folder with the below-naming convention

    FolderPath = "C:\Reports\"
    
    Filename = Sheet4.drpReport.Value
    
    FileDate = Date
    
    FileMonth = Mid(FileDate, 6, 2)
    FileDay = Right(FileDate, 2)

    Filename = Filename & "_" & FileMonth & FileDay

Open in new window



The report name is from a dropdown and then it just adds the date
Group Finance Manager
Commented:
I've incorporated your code

Sub SaveSheets()    '
    Const FolderPath As String = "C:\Reports\"
    Dim sFileName As String, FileMonth As String, FileDay As String
    Dim FileDate As Date

    sFileName = Sheet4.drpReport.Value
    FileDate = Date
    FileMonth = Mid(FileDate, 6, 2)
    FileDay = Right(FileDate, 2)

    FileName = FolderPath & Application.PathSeparator & FileName & "_" & FileMonth & FileDay

    Sheets(Array("Sheet1", "Sheet3", "Sheet4")).Copy

    ActiveWorkbook.SaveAs FileName:=sFileName, FileFormat:=51

End Sub

Open in new window


I would think that you should format the Date correctly, because otherwise the FileMonth and FileDay will not be what you expect.

I also don't think you really need a Variable for the Date

Author

Commented:
Thank you Roy - works perfectly - appreciate your help
Roy CoxGroup Finance Manager

Commented:
Pleased to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial