How to Select Multiple Worksheets in Excel VBA

Posted on by Summa Lai

Sometimes, you may want to select a group of worksheets in Excel. Below is the way how to do this.

Sheets(Array(“sheetname1”, “Sheetname2”, etc…)

Below, please see a sample.

mm = Month(Now) - 1
Sheets("BC008R-2021-" & MonthName(mm)).Select
sht8 = ActiveSheet.Name
Range("E13:K14").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Application.DisplayAlerts = False
Path = ActiveWorkbook.Path & "\"
nfname = "LCRB_Report_2021_" & MonthName(mm) & "_2Nick.xlsx"
Sheets(Array(sht1, sht3, sht5, sht7, sht8)).Copy
ActiveWorkbook.SaveAs Filename:= _
    Path & nfname _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True