Vba code to open multiple files in separate sheets of the same workbook

Question

I have a code that allows me to open a file in an excel workbook, however I want to be able to open multiple files within the same workbook named p00001, p00002, p00003 and so on. Does anyone know how I can edit my code to select all the files named this way and open them in separate sheets in the same workbook?

My code is:

Sub Open_Workbook()

    Dim my_FileName As Variant

    my_FileName = Application.GetOpenFilename

    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If

End Sub

Answer 1

In this solution i used the FileDialog for Selecting Multiple Files. After that, you need to Loop all thoes Files a for Loop. Inside the For Loop, you have to Open the File and Import the Sheet. In this Example i Imported all the Sheets the Workbook has. After the Code is done Importing you close the Source Workbook and do the Same for the Rest of the Files.

Sub Import Files()
        Dim sheet As Worksheet
        Dim total As Integer
        Dim intChoice As Integer
        Dim strPath As String
        Dim i As Integer
        Dim wbNew As Workbook
        Dim wbSource As Workbook
        Set wbNew = Workbooks.Add


        'allow the user to select multiple files
        Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
        'make the file dialog visible to the user
        intChoice = Application.FileDialog(msoFileDialogOpen).Show

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        'determine what choice the user made
        If intChoice <> 0 Then
            'get the file path selected by the user
            For i = 1 To Application.FileDialog(msoFileDialogOpen).SelectedItems.Count
                strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(i)

                Set wbSource = Workbooks.Open(strPath)

                For Each sheet In wbSource.Worksheets
                    total = wbNew.Worksheets.Count
                    wbSource.Worksheets(sheet.Name).Copy _
                    after:=wbNew.Worksheets(total)
                Next sheet

                wbSource.Close
            Next i
        End If

    End Sub

If you want to get all Files From a Directory you can change the ApplicationFile Dialog with a Loop were you Loop the Directory Like This:

 directory = "c:\test\"
    fileName = Dir(directory & "*.xl??")
    Do While fileName <> ""
    'Put Code From For Loop here.
    Loop