Adding a sheet to a "save as" macro

[Mr_Phil]

Hi. I am stumped. I've googled and googled and still haven't hit on the right keyword combination to explain to me what I need to do.
This code takes my template and saves it as a filename based on Q1. It worked fine with minor copy/paste adjustments. But recently I have needed to add a sheet from the original as those items have become more valuable. The sheet name is "inventory calculations".

Basically we open the RSP PO Form Template and want to copy both sheets (Subcontractor PO Form and Inventory Calculations) to the new workbook which is saved as shown below. But, I don't see a clear way to say that in VBA. Any tips or advice is gratefully welcomed. Thank you for looking.

Sub Export()
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Workbooks.Open FileName:=wb.Path & "\RSP PO Form Template New.xlsx"
    With Workbooks("RSP PO Form Template New.xlsx").Sheets("RSP PO Form")
        If .Range("Q1").Value <> "" Then
            Workbooks("DISH ORDER WORKSHEET.xlsb").Sheets("Subcontractor PO Form").Range("H17:H500").Copy
            .Range("H17").PasteSpecial Paste:=xlPasteValues
            .Copy
                  
            ActiveWorkbook.SaveAs FileName:=wb.Path & "\" & .Range("Q1").Value & ".xlsx", FileFormat:=51

 

[DanteAmor]

I suggest you organize in a table, which book-sheet-range (or maybe the whole sheet) you want to copy and where you want to put it.

 

[bebo021999]

You did not tell us which range to copy and which range to paste for 2nd copy.
Assume:

If .Range("Q1").Value <> "" Then
            Workbooks("DISH ORDER WORKSHEET.xlsb").Sheets("Subcontractor PO Form").Range("H17:H500").Copy
            .Range("H17").PasteSpecial Paste:=xlPasteValues
            
            'ADDED PART: second copy: assume copy range("I17:I500") then paste to range "I17")
            Workbooks("DISH ORDER WORKSHEET.xlsb").Sheets("Inventory Calculations").Range("I17:I500").Copy
            .Range("I17").PasteSpecial Paste:=xlPasteValues

                  
            ActiveWorkbook.SaveAs FileName:=wb.Path & "\" & .Range("Q1").Value & ".xlsx", FileFormat:=51

 

[Mr_Phil]

You did not tell us which range to copy and which range to paste for 2nd copy.
Assume:

If .Range("Q1").Value <> "" Then
            Workbooks("DISH ORDER WORKSHEET.xlsb").Sheets("Subcontractor PO Form").Range("H17:H500").Copy
            .Range("H17").PasteSpecial Paste:=xlPasteValues
           
            'ADDED PART: second copy: assume copy range("I17:I500") then paste to range "I17")
            Workbooks("DISH ORDER WORKSHEET.xlsb").Sheets("Inventory Calculations").Range("I17:I500").Copy
            .Range("I17").PasteSpecial Paste:=xlPasteValues

                 
            ActiveWorkbook.SaveAs FileName:=wb.Path & "\" & .Range("Q1").Value & ".xlsx", FileFormat:=51

Perfect. I didn't realize I should have added the sheet in a similar fashion to the first one. Thank you very much for your help.