Import Multi Workbooks to seperate tabs

[fiftimedun]

New to VBA and this is over my head, any assistance would be greatly appreciated:

I have multiple workbooks that i need imported into a single workbook but each workbook must go on to its own tab. I would like the operation to give you the option to search for the folder that contains all of the workbooks (they will be in the same folder, but the folder will change)

Notes:
-Workbooks have a date at the end so the end must be wildcard
-Workbooks must be able to be assigned to copy their data to a tab that has a different name than the origin workbook, eg. workbook "Red", imports to tab "Seven"
--i can manually name and assign the workbook/tab names or provide a sample of what i am working with (there are 21 reports to import, each to seperate tabs)

Here is what i have to search for the folder, but i don't really understand how it works or what to do with it...

Sub test()
    
    MsgBox "Please navigate to the folder containing the files to process."
    
    With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
        On Error Resume Next
        fileLocation = .SelectedItems(1)
    End With
   
    If fileLocation = "" Then
        End
    End If
    
End Sub

Thank you all in advance for your time!

 

[fiftimedun]

You sir... are the man. I don't know how else to thank you and if i could i'd donate to your charity of writing codes for those that are still learning but need to complete something that is beyond their abilities. Again thank you and if you'd like i can send you my final product now that it is complete so you can see what you help me create. (an extreme amount of subs... for me atleast... with many-a-button macro actiavtions etc.)

 

[John_w]

Thanks for that and thanks for letting me know that it works. I'm glad I could help and I hope you've learned a bit of VBA.