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
> >
|
Similar Threads
