the code creates a dialog box to select the source folder and then opens
every XLS file in the folder and puts the sheet into the workbook where the
macro is located. I thought this was easier then to use you list of files in
the workbook.

Sub MakePriceBook()

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&)
On Error GoTo 0

If objFolder Is Nothing Then
MsgBox ("Cannot open directory - Exit Macro")
Exit Sub
End If

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path

Folder = Folder & "/"

FName = Dir(Folder & "*.xls")
Do While FName < ""
With ThisWorkbook
Set oldbk = Workbooks.Open(Filename:=Folder & FName)
oldbk.Sheets("Pricebook Pages").Copy _
after:=.Sheets(.Sheets.Count)
End With
ActiveSheet.Name = FName
oldbk.Close savechanges:=False
FName = Dir()
Loop
End Sub



"Jeff" wrote:

Hi All,

Good day everyone. Can anyone help me? I have one workbook with
filename "Listing Excel Files in a Folder".
I was trying to create a macro in this workbook, which can open all
the excel files in a particular folder. Then copy the sheet with sheet
name "Pricebook Pages" from these excel files and paste it to the
different blank worksheets that i prepared in my workbook "Listing
Excel Files in a Folder".

The excel files in a folder are pricebooks of different customer. The
pricebook data is always in sheet1("Pricebook Pages").

Cheers.

Jeff