Excel VBA Macro - Copying data from multiple sheets in existing file, and creating new file and pasting selected data into separate sheets

Question

any and all help is greatly appreciated.

Description: I have an excel file with several sheets of data. I need to copy ALL of the data from multiple sheets, create a NEW excel file, and paste that data into multiple sheets. So if I have the following:

excelfile1.xlsx - sheet1 - sheet2

I need to select sheet1, copy all the data in sheet1, create a new excel file and paste the sheet1 data into the new file on sheet1new, and then need to do the same with sheet2.

Here is my code for processing 1 sheet (but am not sure how to ask it to process the actions for the 2nd sheet, which is where I need some guidance):

Dim Directory As String
Dim Filename As String

Directory = Range("directory").Value
Filename = Range("filename").Value





Sheets("RHD").Select

Cells.Select
Range("N15").Activate
Selection.Copy

Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:=Directory & Filename, _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

Sheets("I&S").Select

Answer 1

Worksheets(Array("Sheet1", "Sheet2")).Copy
With ActiveWorkbook
 .SaveAs Filename:=Environ("TEMP") & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook 
 .Close SaveChanges:=False 
End With 

from docs.microsoft.com