Retrieving the same cell in multiple workbooks to one master data sheet

[iketown802]

Hi All!

My question is that I have numerous workbooks that an employee keeps record of and keeps data in Cell P1. (Different workbook for every day). I need to retrieve Cell P1 to compile the information into one ongoing running sheet.

Example below:

Shift 1 Dept A in its own workbook drums processed data in cell P1
Shift 2 Dept A in its own workbook drums processed data in cell P1
Shift 3 Dept A in its own workbook drums processed data in cell P1
Shift 1 Dept B in its own workbook drums processed data in cell P1
Shift 2 Dept B in its own workbook drums processed data in cell P1
Shift 3 Dept B in its own workbook drums processed data in cell P1


I want to make a master sheet for Operations to show on a daily basis by dept and shift how many drums were processed (Cell P1).

Any help would be GREATLY appreciated!!

Thank you!!!

 

[DanteAmor]

Where are the books? in the same folder?
What is the sheet where the data of cell P1 is located?
Where do you get the information Shift 1 Dept A?

 

[iketown802]

Where are the books? in the same folder?
What is the sheet where the data of cell P1 is located?
Where do you get the information Shift 1 Dept A?

Each workbook is stored in a folder called "Processed Sheets" (Every day for the year is in there for each department for each shift). Everyone one of these sheets has the number of drums processed for the day for the shift in Cell P1. The information is brought into Excel by scanning a barcode label and that populates a "D000001" number. I'll try to detail out more below:

Folder - Processed sheets

In above folder are the below:

20181212_tank 3_1st shift worksheet (cell P1 is #of drums processed calculation of sum of cells A1 through A***** shows 100)
20181212_tank 3_2nd shift worksheet(cell P1 is #of drums processed calculation of sum of cells A1 through A***** shows 350)
20181212_tank 6_3rd shift worksheet(cell P1 is #of drums processed calculation of sum of cells A1 through A***** shows 42)
20181211_tank 3_1st shift worksheet (cell P1 is #of drums processed calculation of sum of cells A1 through A***** shows 119)
20181211_depack worksheet(cell P1 is #of drums processed calculation of sum of cells A1 through A***** shows 56)
20181211_tank 3_1st shift worksheet(cell P1 is #of drums processed calculation of sum of cells A1 through A***** shows 254)
....continues for everyday for the entire year


In the above sheets in each sheet there is a Cell P1 that has the total # of drums processed for the day for that shift and I want to put into just one sheet to populate daily from retrieving this information to show on one worksheet the drums processed for each shift where dates are the row headers and the column headers are the shifts.

Hope this makes sense.

Thanks for your reply!

 

[DanteAmor]

I understand, you have several books in a folder. I read the first book, but that first book has several sheets, if it has several sheets, what is the name of the sheet from which I should take the data of P1. Or just have one sheet, if so, put the next macro in a book, put the book out of the folder and run the macro.

Sub Retrieving_Cell()
'Retrieving the same cell in multiple workbooks to one master data sheet
    Application.ScreenUpdating = False
    Application.StatusBar = False
    Dim l1 As Workbook, l2 As Workbook, h1 As Worksheet, h2 As Worksheet
    Dim ruta As String, arch As String
    Dim i As Integer
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets(1)
    '
    h1.Rows("2:" & Rows.Count).ClearContents
    ruta = "C:\Processed sheets\"
     'ruta = "C:\trabajo\"
    If Right(ruta, 1) <> "\" Then ruta = ruta & "\"
    '
    arch = Dir(ruta & "*.xls*")
    i = 2
    Do While arch <> ""
        Application.StatusBar = "Reading file : " & arch
        Set l2 = Workbooks.Open(ruta & arch)
        Set h2 = l2.Sheets(1)
        h1.Cells(i, "A").Value = arch
        h1.Cells(i, "B").Value = h2.Range("P1").Value
        i = i + 1
        l2.Close
        arch = Dir()
    Loop
    Set l1 = Nothing: Set l2 = Nothing
    Set h1 = Nothing: Set h2 = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "End Retrieving Cell"
End Sub

Result:
In the workbook whit the macro, in sheet 1, In column A the name of file and in column B the vaue of cell P1.

Regards

 

[iketown802]

I understand, you have several books in a folder. I read the first book, but that first book has several sheets, if it has several sheets, what is the name of the sheet from which I should take the data of P1. Or just have one sheet, if so, put the next macro in a book, put the book out of the folder and run the macro.

Sub Retrieving_Cell()
'Retrieving the same cell in multiple workbooks to one master data sheet
    Application.ScreenUpdating = False
    Application.StatusBar = False
    Dim l1 As Workbook, l2 As Workbook, h1 As Worksheet, h2 As Worksheet
    Dim ruta As String, arch As String
    Dim i As Integer
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets(1)
    '
    h1.Rows("2:" & Rows.Count).ClearContents
    ruta = "C:\Processed sheets\"
     'ruta = "C:\trabajo\"
    If Right(ruta, 1) <> "\" Then ruta = ruta & "\"
    '
    arch = Dir(ruta & "*.xls*")
    i = 2
    Do While arch <> ""
        Application.StatusBar = "Reading file : " & arch
        Set l2 = Workbooks.Open(ruta & arch)
        Set h2 = l2.Sheets(1)
        h1.Cells(i, "A").Value = arch
        h1.Cells(i, "B").Value = h2.Range("P1").Value
        i = i + 1
        l2.Close
        arch = Dir()
    Loop
    Set l1 = Nothing: Set l2 = Nothing
    Set h1 = Nothing: Set h2 = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "End Retrieving Cell"
End Sub

Result:
In the workbook whit the macro, in sheet 1, In column A the name of file and in column B the vaue of cell P1.

Regards

Each book only has 1 sheet in in called "Sheet1"

Thanks!

 

[DanteAmor]

Then test de macro