separate all the sheets from one workbook, but also copy sheet called "Look Ups", then combine them

[Golfboy1996]

Hi, is there a way to separate all the sheets from one workbook, but also copy a sheet called "Look Ups" to each new workbook?

Also, I will need code to recombine them later.


For recombine, once the sheets are in there own workbooks, people will put numbers into a column. Then, I will need to combine them back into one workbook.
- I would not have to separate them, but only one person can have an excel file open at one time.
- I hope this make sense.

 

[Golfboy1996]

ok, so I got it to separate with 2 sheets in one book and save them. One of the sheet is "Look Ups" and the other sheet is just another sheet in the workbook.

Sub CreateNewSBooks()
Dim ws As Worksheet
Dim wbThis As Workbook
Dim wbNew As Workbook
    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        If ws.Name <> "Look Ups" Then
            wbThis.Sheets(Array(ws.Name, "Look Ups")).Copy
            Set wbNew = ActiveWorkbook
            wbNew.SaveAs strFilename
            wbNew.Close
        End If
    Next ws
End Sub

But now, how do you combine a bunch of workbooks into one workbooks?