Save multiple sheets in a new workbook in a particular file format

Question

I tried to read up a few related posts on the forum but wasnt able to make a code work or understand the syntax of a few functions.

I will try to describe what I want to be done in a crisp fashion:

• I have a workbook with the multiple sheets (Sheet1, Sheet2 ... Sheet 5) and I want to create a macro assigned button to Save as a new work book containing only Sheet 1, Sheet 2 and Sheet3

• The file format should be Microsoft Excel 97-2003 Worksheet (.xls)

• On clicking the Macro assigned button the Save as dialogue box should pop up allowing the user to select destination and also optionally a new file name (pre assigned file name can be "textstring123"

• After the workbook is saved the workbook should open for user to inspect while the old workbook is minimised

I am using Excel 2013, in case that is relevant.

The post may seem crude but I have no choice but to seek help from you as I have been breaking my head over this for the last day and a half and without this the rest of my macro project will become a waste. Thank you in advance for and suggestion/advice/ help.

If any other details or clarification is required please do ask.

I have added my lines of code that I have made but doesnt seem to work properly.

Sub Macro6()
'
' Macro6 Macro
' Save as
''
Dim varResult As Variant
Sheets(Array("sheet1", "sheet2", "sheet3")).Copy
varResult = Application.GetSaveAsFilename(FileFilter:= _
             "Excel Files *.xls", FileFormat:=-57, Title:="Save File", _
            InitialFileName:=ActiveWorkbook.Path \ Textstring123.xls)
If varResult <> False Then
ActiveWorkbook.SaveAs Filename:=varResult, _
FileFormat:=xlWorkbookNormal

     Exit Sub
    End If

End Sub

Answer 1

This will do the trick, I have an issue with the Filters so I added a bit of error handling!

Option Explicit

Sub Macro6()
'
' Macro6 Macro
' Save as
''
Dim tB As Excel.Workbook
Dim wB As Excel.Workbook
Dim ExportArray As Variant
Dim ShName As Variant
Dim ExportName As String

Dim varResult As Variant

Set tB = ThisWorkbook
ExportArray = Array("sheet1", "sheet2", "sheet3")

For Each ShName In ExportArray
    Debug.Print ShName
    tB.Sheets(ShName).Copy
    Set wB = ActiveWorkbook
    On Error Resume Next
        ExportName = Application.GetSaveAsFilename(tB.Path & "\Textstring123", "Excel Files *.xls", , "Save " & ShName)
        If Err.Number > 0 Then
            ExportName = Application.GetSaveAsFilename(tB.Path & "\Textstring123", , , "Save " & ShName)
        Else
            'No error, everything went well with filters
        End If
    On Error GoTo 0

    'String 8 and Boolean 11
    If VarType(ExportName) <> 8 Then
        Exit Sub
    Else
        wB.SaveAs Filename:=ExportName, FileFormat:=xlWorkbookNormal
    End If
    DoEvents
    wB.Close
Next ShName

End Sub