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

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

Hi,

thanks I tried using this code for copying files from one folder to
another I get the subscript out of range error when it tries to execute the
code

oldbk.Sheets("Pricebook Pages").Copy _
after:=.Sheets(.Sheets.Count)

could you please help. Thanks.

Pc

"joel" wrote:

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

When you get the error the two workbooks will be opened. go to the workbook
that doesn't have the macro (the one opened by the macro) and check the sheet
names. Make sure there is a sheet named "Pricebook Pages".

"Pc" wrote:

Hi,

thanks I tried using this code for copying files from one folder to
another I get the subscript out of range error when it tries to execute the
code

oldbk.Sheets("Pricebook Pages").Copy _
after:=.Sheets(.Sheets.Count)

could you please help. Thanks.

Pc

"joel" wrote:

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