Import Sheet from a folder and with specific name

[airforceone]

Good day mate, need some guidance again
I would like to copy sheet1 (First Worksheet) in all workbook inside a folder.
I have a running code that only copies One (1) Worksheet in open workbook

Workbook in the Folder
SalesB000120240123.xlsx
ReturnsB000120240123.xlsx
InventoryB000120240123.xlsx
PreOrderB000120240123.xlsx
ArchiveB000120240123.xlsx

Sub ImportMySheet()
    Dim sFileNamePath As String, sFileName As String
    Dim wbTarget As Workbook, wbSource As Workbook
    Dim wsSource As Worksheet
    Dim sDirFilePath As Variant
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wbTarget = ActiveWorkbook
    sFileNamePath = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Source Workbook")
    If sFileNamePath = "False" Then
        MsgBox "Select Source Workbook Now!"
        Exit Sub
    Else
        sDirFilePath = Split(sFileNamePath, "\")
        sFileName = sDirFilePath(UBound(sDirFilePath))
        Application.Workbooks.Open Filename:=sFileNamePath
        Set wbSource = Workbooks(sFileName)
        With wbSource
            If LCase(wbSource.Name) Like "Sales*" Then
                Set wsSource = wbSource.Sheets(1)
                wsSource.Copy After:=wbTarget.Sheets(wbTarget.Sheets.count)
                wbTarget.ActiveSheet.Name = "Sales"
                wbSource.Close SaveChanges:=False
            Else
                wbSource.Close SaveChanges:=False
                MsgBox "Workbook Source does NOT EXIST!", vbOKOnly + vbCritical
            End If
        End With
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
' End of ImportMySheet
End Sub

 

[airforceone]

I must have omitted but also need to rename imported worksheet based on their workbook name

 

[jolivanes]

You're trying to confuse me. Your header says to import sheet with a specific name and in the post it is the first sheet.
Is the "Master" workbook, the workbook where the specific sheets or the first sheets will be copied into, saved in that same folder?
Does not need to be but you can use the path from "Master" workbook in the code.

 

[jolivanes]

If your "Master" is in the same folder, this should do what you requested if you want the first sheet that is.

Sub First_Sheet_Into_Master()
Dim mydir As String, myfile As String, mybook As Workbook, i As Long
    mydir = ThisWorkbook.Path & "\"
    myfile = Dir(mydir & "*.xl*")
    Application.ScreenUpdating = False
    Do While myfile <> ""
        If myfile <> ThisWorkbook.Name Then
            Set mybook = Workbooks.Open(mydir & myfile)
                mybook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                ThisWorkbook.Sheets(Sheets.Count).Name = Left(mybook.Name, InStrRev(mybook.Name, ".") - 1)
            mybook.Close False
        End If
        myfile = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

 

[airforceone]

You're trying to confuse me. Your header says to import sheet with a specific name and in the post it is the first sheet.
Is the "Master" workbook, the workbook where the specific sheets or the first sheets will be copied into, saved in that same folder?
Does not need to be but you can use the path from "Master" workbook in the code.

sorry mate the "Post Title" is my mistake! due to internet conectivity anyway
1. Source Workbook Folder is Dynamic
2. Master Workbook (where the code will be running) is another Folder
3. I would like to import ALL Source Workbook (First Sheet only) TO Master Workbook and rename all imported first sheet to its Workbook name ie.
SalesB000120240123.xlsx = Sheet Sales
ReturnsB000120240123.xlsx = Sheet Returns
InventoryB000120240123.xlsx = Sheet Inventory
PreOrderB000120240123.xlsx = Sheet PreOrder
ArchiveB000120240123.xlsx = Sheet Archive

 

[jolivanes]

If the "Master" is not in the same folder and you need to select the folder with the workbooks.
Replace this line

mydir = ThisWorkbook.Path & "\"

with these lines

With Application.FileDialog(4)
        If .Show Then
            mydir = .SelectedItems(1) & "\"
        Else
            MsgBox "You haven't selected a folder!", vbExclamation
            Exit Sub
        End If
    End With

Change first code accordingly and it should all work as intended.

The sheet naming needs some changing I see. Is the letter B in the workbook name in all workbooks.
If so, change the period in this line

Left(mybook.Name, InStrRev(mybook.Name, ".") - 1)

to a "B" so you'll have

Left(mybook.Name, InStrRev(mybook.Name, "B") - 1)

 

[airforceone]

If the "Master" is not in the same folder and you need to select the folder with the workbooks.
Replace this line

mydir = ThisWorkbook.Path & "\"

with these lines

With Application.FileDialog(4)
        If .Show Then
            mydir = .SelectedItems(1) & "\"
        Else
            MsgBox "You haven't selected a folder!", vbExclamation
            Exit Sub
        End If
    End With

Change first code accordingly and it should all work as intended.

The sheet naming needs some changing I see. Is the letter B in the workbook name in all workbooks.
If so, change the period in this line

Left(mybook.Name, InStrRev(mybook.Name, ".") - 1)

to a "B" so you'll have

Left(mybook.Name, InStrRev(mybook.Name, "B") - 1)

Big Thanks mate!

 

[jolivanes]

Tank you for the like and the follow up and good luck