Summarizing data from several workbooks - VBA?

[Woodjockey]

There are 4 separate assembly areas in our factory. Each area assembles up to 3 different size machines. Each machine has a planned daily production number. Each area has an Excel spreadsheet that shows the planned production numbers for the day. At the end of the shift the operators enter their actual production numbers for the day. Our director would like a summary spreadsheet showing all planned/actual production numbers for the day for each area. Each area has a separate workbook, and each workbook has a separate worksheet for each month. Row 2 shows the day of the month. Under the day of the month the operators enter their daily production numbers.

In summary, I want to be able to pull Planned and Actual data from several spreadsheets based on the date the director types in and summarize on one page. I hope I have included sufficient information. Thank you for your assistance.

 

[ranman256]

you should switch to an access database. The data can reside on a server ,collect the info on all assy areas, on all machines. (ive written this before)
then you can pull any data, any date.

 

[JLGWhiz]

I hope I have included sufficient information

Actually, you haven't.
1. Workbook name that will host the code
2. Each area workbook name
3. Sheet name of sheet containing the production data and for each workbook if not all the same.
4. How the days are entered onto the sheet, ie. columns 2 thru 32 or only work days for the month.
5. Are there gaps in the days for weekends or are they cosecutive production days.
6. If the sheets are named for the month, then how is the month entered? Jan, January, Jan 2018? Are these consistent for all areas.
7. Directory path for the workbooks if they need to be opened and saved and will be in a different directory than the host workbook.
8. Is each workbook layout identical and are there any formulas or merged cells in the ranges to be copied.
That should be enough to get a basic macro compiled.

 

[Woodjockey]

I apologize for the lack of information. I'll try to do better this time.
1. "Daily Productivity" - This workbook will contain all of the summary data. It will only be used by management. It will default to =today()-1.
2. There are 3 assembly areas. They will each have a workbook - "Small Line 2018", "Medium Line 2018", and "Large Line 2018".
3. Within each workbook will have a worksheet for month - "January", "February", etc. where the daily production is recorded.
4. The days of the month are in row 2 in columns D2 thru AH2.
5. There are no gaps in the days as we often work the weekends.
6. Each workbook has separate sheets for each month, as stated above. They naming conventions are consistent for each assembly area workbook.
7. The directory path for the individual assembly area workbooks will be: C:\Building 3\Assembly\Daily Production. All of the assembly workbooks will be in this folder. The actual path for the folder for the management summary workbook has not been setup yet. I will most likely follow a similar path. I can adjust that later.
8. All of the individual assembly workbooks are the same. There are no merged cells where the data resides. There are 4 rows of data of interest: Daily Plan, Daily Actual, Cumulative Plan, and Cumulative Actual. The only formulas are in the cumulative data rows.

 

[JLGWhiz]

This macro will assume that the summary workbook will host the code and will be created or saved as a macro enabled (.xlsm) workbook.
Since I cannot see your workbooks, I will assume the rows of interest are Rows 1:4. Also, column A will Identify the workbook and sheet that the data originated from.

Sub mgmtSummary()
Dim fPath As String, wb As Workbook, sh As Worksheet, wbAry As Variant, sumsh As Worksheet
fPath = "C:\Building 3\Assembly\Daily Production\"
wbAry = Array("Small Line 2018.xlsx", "Medium Line 2018.xlsx", "Large Line 2018.xlsx")
Set sumsh = Workbooks("Daily Productivity.xlsm").Sheets(1) 'Edit sheet name
    For i = LBound(wbAry) To UBound(wbAry)
        Set wb = Workbooks.Open(fPath & wbAry(i))
            Set sh = Sheets(Format(Date, "mmmm"))
            sumsh.Cells(Rows.Count, 1).End(xlUp)(2).Resize(4) = wb.Name & "/" & sh.Name
            sh.Range("D1;AH4").Copy sumsh.Cells(Rows.Count, 1).End(xlUp).Offset(-3, 1)
            wb.Close False
    Next
End Sub

If the wrong rows were copied or if data was pasted to the wrong columns or rows then please specify by column and row reference where you need the data to be copied from and to. Again, your worksheets are not visible on the forum unless you post an image or a link to a share drive, so header references are meaningless in designating columns or rows to copy from and paste to. Although I could attempt to search for the headers, and get the column and row reference, it is not the most accurate way to go.
The code has not been tested, so if there are errors, note the error message, click the 'Debug' button and note the line of code that is highlighted then post that information back here for any changes needed to the code.

 

[Woodjockey]

Thank you very much for your code. I will not be able to try it for at least a week or so. I will be out of the office due to a family crisis. I am anxious to try your macro when I return. I will post to the forum when I had a chance to run your macro. Thank you again for your help.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...g-data-from-multiple-sources.html#post4864612

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Woodjockey]

Thank you for your suggestion. I know very little about Access. If a VBA solution does not work I will look into Access.