T3874_Export Specific Sheets One Workbook Arrays CopyModule

https://excel-egy.com/forum/t3874
---------------------------------

Sub Export_Specific_Sheets_To_One_Workbook_Using_Arrays_CopyModule()
    Dim ws          As Worksheet
    Dim sSheets()   As String
    Dim n           As Long

    Application.ScreenUpdating = False
        For Each ws In Worksheets(Array("Sheet1", "Sheet4"))
            n = n + 1
            ReDim Preserve sSheets(1 To n)
            sSheets(n) = ws.Name
        Next ws

        Worksheets(sSheets).Copy
        Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Output", FileFormat:=52
            CopyModule ThisWorkbook, "Module2", ActiveWorkbook
        Application.DisplayAlerts = True

        For Each ws In ActiveWorkbook.Worksheets
            ws.UsedRange.Value = ws.UsedRange.Value
        Next ws

        ActiveWorkbook.Close True
    Application.ScreenUpdating = True

    MsgBox "Done...", 64
End Sub

Sub CopyModule(sourceWB As Workbook, strModuleName As String, targetWB As Workbook)
    Dim strFolder   As String
    Dim strTempFile As String

    strFolder = sourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"

    On Error Resume Next
        sourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
        targetWB.VBProject.VBComponents.Import strTempFile
        Kill strTempFile
    On Error GoTo 0
End Sub