Copy Cell Values from all files in a folder to master file

[sspatriots]

Hi,

I found the code below and tried to tweak it to go into a folder of multiple files and copy certain cell values back to the master file. Not sure what I've done wrong but when it gets to the point where it is supposed to start opening the files and copying the values it just skips to the end of the macro. Any assistance on where I am going wrong would be greatly appreciated.

Sub ImportInfo()
    Dim sPath As String 'path of folder containing info
    Dim sFileName As String '
    Dim wsSummary As Worksheet 'worksheet to paste data to in this workbook
    Dim wsData As Worksheet 'sheet with data to copy
    Dim wb As Workbook 'workbooks to loop thorugh
    Dim nr As Long 'next row to add the data
    
    
    'Get the worksheet to add the info to
    Set wsSummary = ThisWorkbook.Worksheets("Sheet1")
    
    'first row is 2
    nr = 2
    
    sPath = "C:\Users\Documents\PROJECTS\" '[COLOR=#ff0000][B]Change as required[/B][/COLOR]
    
    sFileName = Dir(sPath & "*.xlsm")
    
    Do While sFileName <> ""
        'open workbook
        Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
        'get the sheet to copy from
        Set wsData = wb.Sheets("DEM Release Form")
        'get the data
        wsSummary.Range("A" & nr).Value = wsData.Range("C3").Value
        wsSummary.Range("B" & nr).Value = wsData.Range("B8").Value
        wsSummary.Range("C" & nr).Value = wsData.Range("F8").Value
        wsSummary.Range("D" & nr).Value = wsData.Range("C10").Value
        wsSummary.Range("E" & nr).Value = wsData.Range("G10").Value
        wsSummary.Range("F" & nr).Value = wsData.Range("B9").Value
        wsSummary.Range("G" & nr).Value = wsData.Range("I9").Value
     
        'get next row
        nr = nr + 1
        'close the workbook
        wb.Close
        'get next workbook name
        sFileName = Dir
    Loop
    
    
End Sub

 

[Fluff]

Is your file path correct?
I would have expected to see a username in there.

 

[sspatriots]

I was just about to say that I had the file path incorrect. Thank you. That problem is resolved, however, I have another associated with this same code, if it is ok to add in here. The topic still applies and its the same code.

This worksheet that I'm copying has a couple checkboxes on there, like "CheckBox1" and "CheckBox2". The master file I'm copying to has columns called "In-Ground" and "Holeless", that are respective to the two checkboxes. Somehow, I need to find a way to show the word "Yes" under one of the two destination file column headings based on which of the two checkboxes is checked in the file I'm extracting the data from.

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks

 

[sspatriots]

Forgot to mention, that I would also like this thing to loop through all the worksheets in the source workbooks to gather everything in the same cells on each worksheet. Right now, it only does the one worksheet and I'm finding that it is now hanging up when it gets to a workbook that has more than one worksheet and the worksheets have different names..

 

[sspatriots]

As that is a totally different question, it needs a new thread. Thanks

I can do that. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.