Take 1 worksheet from an workbook and save it as a new excel document

Question

I have a workbook, saved as xyz.xlsm. This workbook has 3 worksheets (sheet1, sheet2, sheet3). I want to create a macro in VBA that will copy sheet3 and save it in a new excel document in path G:\Michael\work\uploadfiles. I want the macro to name this new document the current date + text content in cell D3 from sheet2, ie. (01/02/15 Mike Jones).

Answer 1

I haven't tested it, but something like this should work.

Define your current book as a variable:

Set thisBook = ActiveWorkbook

Then you need to create the new Excel file:

Set newBook = Workbooks.Add

Then copy sheet3 from thisBook to newBook:

thisBook.Sheets("sheet3").Copy After:=newBook.Sheets("Sheet1")

To save the new one, do something like:

dim filePath, thisDate as string
' VBA probably won't like the slashes in the date, so replace them.
thisDate = Replace(Date(),"\"," - ")
filePath = "G:\Michael\work\uploadfiles\" & thisDate & "Mike Jones.xlsx"
newBook.SaveAs FileName:= filePath

Answer 2

The below code does the job.

By default, save1sheet saves the sheet you indicate from the workbook containing the macro, but you can chose the active workbook (if that is different) or any open workbook.

Public Sub save1sheet(sheetNameOrNumber, toFile As String, Optional fromBook = "")
    Dim theBook As Workbook, theSheet As Worksheet
    If fromBook = "" Then
        Set theBook = ThisWorkbook
    ElseIf fromBook = "_active_" Then
        Set theBook = ActiveWorkbook
    Else
        Set theBook = Workbooks(fromBook)
    End If

    Set theSheet = theBook.Sheets(sheetNameOrNumber)
    theSheet.Copy
    ActiveWorkbook.Close True, toFile
End Sub

Sub saveTheSheets()
    Dim pathTemplate As String, path As String
    pathTemplate = "C:\Temp\{date} {person}.xlsx"
    ' G:\Michael\work\uploadfiles

    path = Replace(pathTemplate, "{date}", Replace(Date, "/", " - "))
    path = Replace(path, "{person}", "Mike Jones")
    save1sheet "sheet3", path

    path = Replace(pathTemplate, "{date}", Replace(Date, "/", " - "))
    path = Replace(path, "{person}", "Dirk Horsten")
    save1sheet 2, path, fromBook:="_active_"

    path = Replace(pathTemplate, "{date}", Replace(Date, "/", " - "))
    path = Replace(path, "{person}", "Hamilton Blake")
    save1sheet "Sheet1", path, fromBook:="ThatOtherBook.xlsx"
End Sub