Macro to copy sheets from multiple workbooks

[petes]

Hi Friends!!

I have one Master file (MAIN.xlsx) and other files (A.xslx, B.xslx, C.xslx........). All these files are in the same folder (C:\test)

I need a macro in MAIN.xlsx file, so that it should copy all the data only from sheet1 of other files (A.xslx, B.xslx, C.xslx........) and paste it after the sheet1 of MAIN.xlsx file along with their sheet names.

Also, the other files (A.xslx, B.xslx, C.xslx........) should be closed after this operation.

Your help is very much appreciated...!!

 

[petes]

Hey Vog!! I am back, since i need some further enhancements to this code.

Currently this code is selecting only the first sheet from different files of Spreadsheet that needs to be consolidated. I need to modify little bit so that:

1) It selects only the last two sheets from different files of spreadsheet with range Row 2 till down and put it into main sheet one below the other.
2) Is it possible to get the File Name from where the sheets are pulled and put this info in Column 1

Sub ImportFiles()
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook
With ThisWorkbook
    MyFolder = .Path
    MyFile = Dir(MyFolder & "\*.xlsx")
    Do While MyFile <> ""
        If MyFile <> .Name Then
            If WorksheetExists(.Name, Left(MyFile, Len(MyFile) - 5)) Then
                .Sheets(Left(MyFile, Len(MyFile) - 5)).UsedRange.ClearContents
             Else
                .Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = Left(MyFile, Len(MyFile) - 5)
            End If
            Set wb = Workbooks.Open(MyFolder & "\" & MyFile)
            wb.Sheets(1).UsedRange.Copy Destination:=.Sheets(Left(MyFile, Len(MyFile) - 5)).Range("A1")
            wb.Close False
        End If
        MyFile = Dir
    Loop
End With
End Sub

Function WorksheetExists(WBName As String, WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Workbooks(WBName).Worksheets(WSName).Name = WSName
End Function