Copying excel sheets to a new file and hiding them

Question

I am trying to copy some sheets to a new file using excel VBA, and i need to hide some sheets (e.g. input sheet, "Market PnLs (Market Place)", "Markets Graph (Market Place)") I have written a VBA to copy the sheets but i do now know how to hide the above sheets in the new file, Can anyone help me with the codes please?

Sub split_online()
Dim onlinepath As Range
Set onlinepath = ThisWorkbook.Sheets("Input").Range("G33")
With ActiveWorkbook.Sheets(Array("P&L Metrics (Ecomm- Global)", "Market PnLs (Online)", _
        "Markets Graph (Online)", "Market Totals (Online)", "GC(Online)", "Apac(Online)", _
        "EMEA (Online)", "AM(Online)", "P&L vs LE (Online)", "P&L vs PY (Online)", _
        "Market PnLs (Market Place)", "Markets Graph (Market Place)", _
        "Market Totals (Market Place)", "GC(Market Place)", "Apac (Market Place)", _
        "EMEA (Market Place)", "AM (Market Place)", "P&L vs LE (Market Place)", _
        "P&L vs PY (Market Place)", "Input", "Input"))
    .Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & onlinepath, FileFormat:=51
End With
End Sub

Answer 1

Create a new workbook and then copy the sheets there. Finally hide the relevant sheets.

Is this what you are trying? (UNTESTED)

Option Explicit

Sub split_online()
    Dim onlinepath As Range
    Dim wbThis As Workbook
    Dim wbThat As Workbook
    
    Set wbThis = ThisWorkbook
    
    Set onlinepath = wbThis.Sheets("Input").Range("G33")
    
    '~~> Create a new workbook
    Set wbThat = Workbooks.Add
    
    '~~> Copy the relevant sheets to new workbook
    wbThis.Sheets(Array("P&L Metrics (Ecomm- Global)", "Market PnLs (Online)", _
                  "Markets Graph (Online)", "Market Totals (Online)", "GC(Online)", "Apac(Online)", _
                  "EMEA (Online)", "AM(Online)", "P&L vs LE (Online)", "P&L vs PY (Online)", _
                  "Market PnLs (Market Place)", "Markets Graph (Market Place)", _
                  "Market Totals (Market Place)", "GC(Market Place)", "Apac (Market Place)", _
                  "EMEA (Market Place)", "AM (Market Place)", "P&L vs LE (Market Place)", _
                  "P&L vs PY (Market Place)", "Input")).Copy Before:=wbThat.Sheets(1)
        
    With wbThat
        '~~> Hide relevant sheets
        .Sheets(Array("Market PnLs (Market Place)", "Markets Graph (Market Place)")).Visible = False
        .SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & onlinepath, FileFormat:=51
    End With
End Sub

If you do not want the default sheet which was created with Workbooks.add then you can try this as well.

Option Explicit

Sub split_online()
    Dim onlinepath As Range
    Dim wbThis As Workbook
    Dim wbThat As Workbook
    Dim shtCount As Long, i As Long
    Dim shtToDelete As String
    
    Set wbThis = ThisWorkbook
    
    Set onlinepath = wbThis.Sheets("Input").Range("G33")
    
    '~~> Create a new workbook
    Set wbThat = Workbooks.Add
    shtCount = wbThat.Sheets.Count
    
    '~~> Delete sheet(s) created by default.
    Application.DisplayAlerts = False
    On Error Resume Next
    For i = wbThat.Sheets.Count To 1 Step -1
        wbThat.Sheets(i).Delete
    Next i
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    '~~> Get the name of the sole sheet in the new workbook
    shtToDelete = wbThat.Sheets(1).Name
    
    '~~> Copy the relevant sheets to new workbook
    wbThis.Sheets(Array("P&L Metrics (Ecomm- Global)", "Market PnLs (Online)", _
              "Markets Graph (Online)", "Market Totals (Online)", "GC(Online)", "Apac(Online)", _
              "EMEA (Online)", "AM(Online)", "P&L vs LE (Online)", "P&L vs PY (Online)", _
              "Market PnLs (Market Place)", "Markets Graph (Market Place)", _
              "Market Totals (Market Place)", "GC(Market Place)", "Apac (Market Place)", _
              "EMEA (Market Place)", "AM (Market Place)", "P&L vs LE (Market Place)", _
              "P&L vs PY (Market Place)", "Input")).Copy Before:=wbThat.Sheets(1)
      
    With wbThat
        '~~> Delete the default sheet
        Application.DisplayAlerts = False
        wbThat.Sheets(shtToDelete).Delete
        Application.DisplayAlerts = True
        
        '~~> Hide relevant sheets
        .Sheets(Array("Market PnLs (Market Place)", "Markets Graph (Market Place)")).Visible = False
        .SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & onlinepath, FileFormat:=51
    End With
End Sub