copying sheet1 from different workbooks contained in a folder to m
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
|