save active sheet and one other sheet too 1

[coolcarls]

I am using the following code to save the active sheet as a new workbook


ActiveSheet.Copy
ActiveSheet.Name = sht_name
filesavename = Application.GetSaveAsFilename(InitialFileName:=ActiveSheet.Name, _
filefilter:="Excel Workbook (*.xls), *.xls&quot..............


I need to save one other sheet from the same workbook along with the active sheet.

Thanks

 

[bryanbayfield]

It's late and I want to go to the the pub, but this should help:

Option Explicit

Private Sub Save_two_sheets()
Dim shts As Variant, rtn_shts As String, file_name As String
ask_question:
    rtn_shts = Application.InputBox(Prompt:="Enter the names of the two sheets to copy" _
                                & vbCrLf & "SEPERATED BY ONE COMMA ALONE", Type:=2)
On Error GoTo wrong_entry
shts = Array(Left(rtn_shts, InStr(rtn_shts, ",") - 1), _
                                Right(rtn_shts, Len(rtn_shts) - InStr(rtn_shts, ",")))
Sheets(shts).Copy
file_name = Application.GetSaveAsFilename(InitialFileName:=ActiveSheet.Name, filefilter:="Excel Workbook (*.xls), *.xls")
ActiveWorkbook.SaveAs (file_name)
Exit Sub
wrong_entry:
    MsgBox Prompt:="The sheets you have entered appear to be invalid." & vbCr _
                            & "Please reselect", Buttons:=vbExclamation
    Resume ask_question
End Sub

I don't think there's an easier way round it than to ask which two you want copied.