Selecting and Copying a Group of Sheets to a new Workbook using VBA

[gpippin84]

Hey guys,

I'm very new to using VBA. My goal here is to be able to select a changing list of sheets using the vba and copy them into a new workbook. I have a standard position for the sheet names but the sheet list could be 5 sheets (as it is in the code below) or it could be 25. Is there a way to select only the cells that have sheet names and put them in the sheet array to copy to a new workbook?

 Sub ExportBranch()

    Dim FirstSheet As String
    With ActiveSheet
        FirstSheet = .Range("B279").Value
        End With
    Dim SecondSheet As String
    With ActiveSheet
        SecondSheet = .Range("B280").Value
        End With
    Dim ThirdSheet As String
    With ActiveSheet
        ThirdSheet = .Range("B281").Value
        End With
    Dim FourthSheet As String
    With ActiveSheet
        FourthSheet = .Range("B282").Value
        End With
    Dim FifthSheet As String
    With ActiveSheet
        FifthSheet = .Range("B283").Value
        End With
        
    Sheets(Array(FirstSheet, SecondSheet, ThirdSheet, FourthSheet, FifthSheet)).Copy



' second half of the VBA after the sheets are copied

    Sheets(SecondSheet).Select
    Range("C264:N273").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C134:N140").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Sheets(SecondSheet).Select
    Dim fname As String
    With ActiveSheet
        fname = .Range("B284").Value
    ActiveWorkbook.SaveAs Filename:= _
        "\\server\folder\folder\2018 Folders & WP\2018 Budget\#Export Files\" & fname, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWindow.Close
    End With


End Sub

This also shows where I'm copying and pasting some values and using a stored name as the file name. I'm not sure how to go to SheetSix, which would be cell b284, only if there is a value in it.

If I've left gaps in my explanation please let me know.

thanks in advance!

gpippin84