VBA - Button Export Multiple Worksheets

[Merco]

Dear Excel Gurus,

I have an Excel file that splits Bill of Materials (BOM) explosions into the correct templates for our ERP system. We copy-paste the BOM of our customers in worksheet "BOM INSERT", we fill in a few data points in "INSTRUCTIONS", and the data automatically fills in Worksheets "BOM UPLOAD" and "PRODUCT UPLOAD".

I want a button in the "INSTRUCTIONS" tab and if you click on it, it simply downloads worksheets "BOM UPLOAD" and "PRODUCT UPLOAD" as separate files. I managed to find a few examples online and on this forum on how to do so, but it does not seem to work the way I want it to. With one I have to define a path, but that path is not universal for everyone that will use this file, it just needs to come in their Download folder for easy and quick upload into the ERP system. It should work for Mac and Windows users. It would be ideal if it would download like you would download something from online, it just jumps to Downloads, but I did not manage to find anything remotely resembling that so I think that is not possible. I am fine with it following the path of the file itself and that these two worksheets save next to it in the same folder. The second issue was that I could not figure out how to download two worksheets instead of just one.

I hope anyone can help, thank you greatly!

 

[Merco]

Done. Only for Mac users now though

Sub Splitbook()
    Dim xPath As String, xWs As Worksheet
    
     xPath = "/users/" & Environ$("USER") & "/Downloads/"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In ThisWorkbook.Worksheets
        If xWs.Name = "BOM UPLOAD" Or xWs.Name = "PRODUCT UPLOAD" Then
            xWs.Copy
            Application.ActiveWorkbook.SaveAs FileName:=xPath & xWs.Name & ".xlsx"
            Application.ActiveWorkbook.Close False
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Workbooks BOM UPLOAD & PRODUCT UPLOAD downloaded to: " & vbNewLine & xPath
End Sub