Consolidate specific cells from all files, all sheets in specific directory

[rdmea1]

Hello,

I've been reading through the various codes for consolidating data from multiple files in a given directory, but nothing seems to be exactly what I'm trying to do. Here's the situation: I have a master consolidation workbook titled "Master Backlog", in a worksheet called "Backlog". I want to look in the directory (C:\Users\rmartin\Desktop\Contract Backlog) and from every file and every sheet in those files copy the following information: Sheet Name, Cell C1, Cell J9, Cell J8

The above specific information would be copied from the source worksheets/workbooks into the "Backlog" worksheet, starting in cell A2 and moving down a row as each worksheet is copy and pasted.

Would appreciate any push in the right direction.

Regards

 

[Ken Cowen]

The following should get you started. It loops through a folder identified by str and puts some information in the cells of the active sheet. So, make sure the active sheet is not one that can't be written over.

Sub test()

Dim wb As Workbook
Dim ws As Worksheet
Dim thisws As Worksheet
Dim fn As String
Dim str As String
Dim i As Integer

Set thisws = ActiveSheet

str = "C:\work\test\"

fn = Dir(str & "*.xl*")

i = 1

Application.ScreenUpdating = False

Do While fn <> ""
    
    Application.DisplayAlerts = False
    Workbooks.Open str & fn
    
    Set wb = ActiveWorkbook

    For Each ws In wb.Sheets
    
        thisws.Cells(i, 1).Value = wb.Name
        thisws.Cells(i, 2).Value = ws.Name
        thisws.Cells(i, 3).Value = ws.Range("c1").Value
        thisws.Cells(i, 4).Value = ws.Range("j9").Value
        thisws.Cells(i, 5).Value = ws.Range("j8").Value
        
        i = i + 1
        
    Next ws

    wb.Close
    
    fn = Dir

Loop

Application.DisplayAlerts = True

End Sub

I hope this helps.

Ken

 

[rdmea1]

Ken,

Thank you for the quick reply! That does just what I need and saves me hours of consolidation. Only tweak I made was to set i=2, instead of i=1 so that it would start the copy process on row 2 of my "Backlog" worksheet.

Thanks again!

Rob

 

[Ken Cowen]

appreciate the feedback, good luck with your project.
Ken