Copy Worksheets into new workbook with Array of Sheet names

[PritishS]

Hi,
I'm trying to Copy Worksheets into new workbook with Array of Sheet names with sheet names as variable.
I wrote a working code as below. In this code I have mentioned Sheet names in a array. But I want to save those sheet name in other sheet and use as a variant in array, which is giving Error 13. Type mismatch

This is working code-

Sub Report_Save()
Dim answer as Interger
Application.ScreenUpdating = False

'Make report with Other sheets
Sheets(Array("Sheet1", "Sheet2", "Sheet2", "Sheet4")).Copy

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=CreateObject("WScript.Shell").specialfolders("Desktop") & "\report_Backup.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
Sheets(1).Activate
Windows("report_Backup.xlsx").Close SaveChanges:=True

Application.DisplayAlerts = True

answer = MsgBox("Report Project Backup Done Successfully")

Application.ScreenUpdating = True
End Sub

The below code I tried, but failed

Sub Report_Save()

Dim SheetNamesFBB As Variant

Set SheetNamesFBB = ThisWorkbook.Sheets("DEFAULTSHEET").Range("DB36") 'in Range("DB36") I have saved sheet names as "Sheet1", "Sheet2", "Sheet2", "Sheet4" in a cell

Application.ScreenUpdating = False

'Make report with Other sheets

Sheets(Array(SheetNamesFBB )).Copy 'I'm getting run-time error 13 Type mismatch in this line

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=CreateObject("WScript.Shell").specialfolders("Desktop") & "\report_Backup.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
Sheets(1).Activate

Windows("report_Backup.xlsx").Close SaveChanges:=True

Application.DisplayAlerts = True

answer = MsgBox("Report Project Backup Done Successfully")

Application.ScreenUpdating = True

End Sub

Is there any way I can set sheet names in 1 cell in other sheet and use those in a array to copy thos sheet in a new workbook? Those sheet names "Sheet1", "Sheet2", "Sheet2", "Sheet4" will not be same every time, thats why I want to define sheet names in a cell.
Thanks in advance for any help in this matter.
Regards
PritishS

 

[AlphaFrog]

Set SheetNamesFBB = ThisWorkbook.Sheets("DEFAULTSHEET").Range("DB36") 'in Range("DB36") I have saved sheet names as "Sheet1", "Sheet2", "Sheet2", "Sheet4" in a cell

Above is not an array variable. It is a Range variable. You set the Variable to be a Cell object DB36.

Try something like this below. Do no put quotes around the sheet names e.g. Sheet1, Sheet2, Sheet3

SheetNamesFBB = Split(ThisWorkbook.Sheets("DEFAULTSHEET").Range("DB36").Value , ", ")

This takes the text of the DB36 cell value and splits it into an array using a comma and space as the delimiter.

 

[Fluff]

How about

Dim SheetNamesFBB As Variant

SheetNamesFBB = Split(ThisWorkbook.Sheets("DEFAULTSHEET").Range("DB36"), ", ")

Application.ScreenUpdating = False

'Make report with Other sheets

Sheets(SheetNamesFBB).Copy

and the sheet names need to be entered like

Sheet1, Sheet2, Sheet2, Sheet3

 

[PritishS]

Dear AlphaFrog & Fluff,

Thanks for the solution. Both worked perfectly.
Thank you very much.
Have a Nice Day!!

Regards
PritishS

 

[Fluff]

Glad we could help & thanks for the feedback.