How do I open sheets and consolidate them into 1 file with multiple sheets?

Question

I have a user to input 20 store numbers into cells A1 to A20. Cells B1 to B20 will have the hyperlink that each store number has, for example: cell B1 to B20 will have the following formula: =HYPERLINK("C:\"Store Number "&A1&".xls")

1. How do I open all these hyperlinks at once without having to click cells B1 to B20 (20 times)?
2. What if the user only wants to see 10 files instead of 20? If I create a VBA that opens all hyperlinks in cells B1 to B20 but the user only input 10 stores, I will have a debug problem.
3. After these files are open, how do I consolidate them into 1 file with up to 20 sheets/tabs? Each store file has the same format and has the same sheet name: "Sheet1".

Thanks so much for the help!!

Answer 1

and welcome to StackOverflow. I found a good answer to your questions 1 & 2 here. (But as the OP commented, these are for web hyperlinks, not file hyperlinks.)

I have not solved Q 3 with VBA, but I have done this manually, by right-clicking on the tab to be copied and then choosing "Move or copy" in the context pop-up. I then copy the sheet to the newly-created book.

EDIT: I would suggest that you change the Hyperlinks to just plain text. Then the following VBA will open the file names in the selected cells.

Dim fileName As String
For Each vCell In Selection
    fileName = vCell.Value
    Workbooks.Open fileName
Next vCell

Answer 2

This might get you started for part 3.

Sub ConsolidateAllSheet1()

    Dim wbCons As Excel.Workbook
    Dim wb As Excel.Workbook
    Dim wbName As String

    Set wbCons = Workbooks.Add()
    For Each wb In Application.Workbooks
        wbName = wb.Name
        If wbName Like "Store Number*" And _
           wbName <> ThisWorkbook.Name Then

           wb.Sheets("Sheet1").Copy _
                 after:=wbCons.Sheets(wbCons.Sheets.Count)
           wbCons.Sheets(wbCons.Sheets.Count).Name = _
                   Replace(wbName, ".xls", "")

           'wb.Close False 'save without changes
        End If
    Next wb

End Sub