Help to copy sheet from another file to another

[sbv1986]

I have 10 file with the same sheet(1) name like "PL01" ..... "PL10"

I want to copy all sheet into one workbook, all sheets has just copied between sheet(First) and sheet(Finals).
This mean the sheets like this:
Before copy Workbook have: MAIN, First, Finals
Aftercopy Workbook have: MAIN, First, PL01, PL02,.....,PL03, PL10, Finals

How can I do this with VBA?? Thanks./.

 

[bsquad]

are you looking to start on PL01 or MAIN? Are you also looking to overwrite the data in your master workbook? or overwrite each time you run it or are you looking to aggregate it?

 

[sbv1986]

are you looking to start on PL01 or MAIN? Are you also looking to overwrite the data in your master workbook? or overwrite each time you run it or are you looking to aggregate it?

Thanks for your replay:

I want to start on PL01 and delete old sheets("PL01",..."PL10") if exist before copy and add new

 

[bsquad]

This should get you started, the code I have commented out or towards the first few lines of code, I started on a delete statement if they exist but will let you finish it.

Sub PL01()
    Dim eWorkbook, iWorkbook As Workbook
        Set eWorkbook = ThisWorkbook
    Dim eSheet As Worksheet
    Dim i, z, t As Long
    Dim iWorkbookImportOpen As Variant
    
    Application.DisplayAlerts = False: Application.AskToUpdateLinks = False: Application.ScreenUpdating = False
'    If ThisWorkbook.Worksheets.Count > 3 Then
'        For t = ThisWorkbook.Worksheets.Count - 1 To 3 Step -1
'            ThisWorkbook.Worksheets(t).Delete
'        Next t
'    End If
    ChDir eWorkbook.Path
    iWorkbookImportOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm; *.xls; *.xltm), *.xlsx; *.xlsm; *.xls; *.xltm", _
                                    Title:="Select Import File", MultiSelect:=True)
                                    On Error Resume Next
        For i = LBound(iWorkbookImportOpen) To UBound(iWorkbookImportOpen)
            Set iWorkbook = Workbooks.Open(Filename:=iWorkbookImportOpen(i), ReadOnly:=True)
            iWorkbook.Worksheets(1).Activate
            With iWorkbook.Worksheets(1)
                Range(Cells(1, 1), Cells(iWorkbook.Worksheets(1).UsedRange.Rows.Count, iWorkbook.Worksheets(1).UsedRange.Columns.Count)).Copy
            End With
            iWorkbook.Application.WindowState = xlMinimized
                eWorkbook.Activate
                With eWorkbook
                        Set eSheet = .Worksheets.Add(After:=.Worksheets(2))
                        eSheet.Name = iWorkbook.Worksheets(1).Name
                End With
                        With eWorkbook.ActiveSheet.Cells(1, 1): .PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False: End With
                            Application.CutCopyMode = False
                        iWorkbook.Close SaveChanges:=False
        Next i
    Application.DisplayAlerts = False: Application.AskToUpdateLinks = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationAutomatic


End Sub