NEW PROSPECT FORM TEST.xlsprospect database 2012 V3.xlsHello,

Im a busy bee on this forum today!!

2 members have helped me out with code today, and its been fantastic. I am so close to finishing my project now.

I have a master spreadsheet (attached)

I receive about 15 - 20 workbooks a day via email. I open these manually, copy and paste the data, save the workbooks etc, and it was taking up alot of time.

My plan now, is to open all these workbooks at the end of the day, and then click a command button and it will populate the cells with data from all the open workbooks.


so far, the macro SAVES all the workbooks to a folder, and then pastes the filename in column A as a hyperlink to the file. GREAT!

the cells are populated using INDIRECT function.


Now, the issue I am encountering, is;

Sub Savefileshyper()
Dim wk As Workbook
Dim fpath As String
Dim uRows As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

fpath = "Z:\NEW PROSPECTS\Completed Prospect forms 2012\"

For Each wk In Workbooks
    With wk
        If .Name <> ThisWorkbook.Name And LCase(Left(.Name, 8)) <> "personal" Then
            uRows = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
            ThisWorkbook.Worksheets(1).Cells(uRows, 1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(uRows, 1), fpath & .Name, , , .Name
            .SaveAs (fpath & wk.Name)
            .Close
        End If
    End With
Next wk

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

this macro does its job, BUT closes the files after saving them, which of course, returns a #REF error in the cells containing INDIRECT fuctions (as the workbooks are now closed)

I removed the .close from the code, which works in the short term, but then obviously I need to close them all.

so, my question here is,

can anyone work with me on this and help me amend the code so either,

1) It PASTES VALUES before closing the individual workbooks
2) remove the .close code and add some more code to the end of the macro to PASTE VALUES on all non blank cells and close all workbooks
3) Suggest any other , more efficient ways of achieving what I want to achieve.

Im led to believe that there IS NOT a function to retrieve data from closed workbooks?


all help is greatly appeciated.


MASTER workbook attached
MOCK prospect form attached


thanks in advance,

Mike.