Monthly Production Report -Automatic transfer of data

Hello Ganesh,

I've had a look at your sample workbook and played around with it for the last couple of days as time has permitted me to do so. There are a number of issues with the workbook but I'm assuming that you quickly threw the sample together so that we had an idea of what you intend to do.

The issues created problems with the VBA programming, but I've made a simple sample of your sample to see if it is something close to what you'd like to do. The sample gathers the information for any Operator from the monthly source sheets and displays them in the MPA sheet and also displays, in Column A, which sheet the data has come from. This is just a starting point, that's why you don't see the charts or totals in the MPA sheet.

Let me know if we are on the right track and I'll look at the rest of it before too long fo you. Please also advise if the sample that you supplied is an exact replica of your actual workbook. This is critical to finding a suitable solution. If it is not, then please supply a sample that is an exact replica with data in all columns filled in. Use dummy data if your actual data is sensitive. Five or six rows of data will suffice.

You'll find my dummy sample here.

Cheerio,

vcoolio.

Hello Sir,

The issues created problems with the VBA programming, but I've made a simple sample of your sample to see if it is something close to what you'd like to do. The sample gathers the information for any Operator from the monthly source sheets and displays them in the MPA sheet and also displays, in Column A, which sheet the data has come from. This is just a starting point, that's why you don't see the charts or totals in the MPA sheet. ----- The sample you have done is exactly what I am looking for, but the only change is in the date. i.e., column A in day 1, day 2, day 3, the date same for all operators.. ( attached sample sheet link).. remaining everything is fine.

Also column C4 is not more required because we are putting the operator name in the cell : I3.

Also I am ensure that, we are in same track and please continue to make the final product of the MPA sheet. When we select the operator name and click on Run me button, the MPA sheet will show the operators report from  01-08-2023 to 31-08-2023.

Please note that I have 2 more support sheets ( Support sheet1, Support Sheet2) in the same file to get more detail.. The run me button should not select the data from these sheets.

Link: https://wetransfer.com/downloads/9ebed45cc1c0018c402154530350f1fa20230828053011/2bdd7b

Regards,

Ganesh

Hello Ganesh,

Very good, We're on track.

Now, as I mentioned in my previous post, there a number of issues withe the original sample workbook which you sent us. Generally, the issues are:-

1) Disjointed data in varying columns.

2) Incorrect syntax in some of the formulae that you have used.

3) The worksheet names you have used (e.g. 01-08-23, 21-08-23 etc..).

4) Merged cells. They cause no end of problems for programming.

To rectify these problems so that we have a smooth running program, we need you to supply a new sample workbook with these corrections:-

For item (1) above:

Fill in all columns with data. Do not leave blank cells and ensure that any formulae refer to the same row.

For item (2) above:

Some of your formulae return numeric results (presumably for the charts to use). However, when you look at, say, the following formula in the source sheets:

=IF(B8="A","8",IF(B8="B","8",IF(B8="C","8",IF(B8="General","8","11"))))

you have the required numeric results (8 and 11 in this case) within quotation marks(" "). Although you will still get the required result (8 or 11) it will appear as a text value in the required cell, not a numeric value of 8 or 11. This in turn will affect the required results for your charts as you cannot sum a text value. Hence, remove all the quotation marks from all the formulae. Thus, the above formula should look like this:

=IF(B8="A",8,IF(B8="B",8,IF(B8="C",8,IF(B8="General",8,11))))

You will need to check all the formulae in your workbook.

For item(3) above:

The source sheet names are dates. Some are dates as string values others are custom formatted dates. This creates a problem when the source sheet names are copied/pasted to the sheet reference column, Column A in the Monthly Production Analysis sheet. Some come through formatted differently as text values but others come through as date values. This will cause confusion for any User trying to reference a source worksheet. The best option here is to use text values only for the sheet names, such as Aug 21, 2023.

For item(4) above:

Unmerge all merged cells. You can still format cells without merging them.

So, with the new sample workbook, make sure that it is a brand new one (there is further corruption within your existing one). Manually create a few source worksheets (three or four will do) set out exactly as your current workbook. Have four or five rows of data per source worksheet as this will suffice for testing. Ensure that any formulae are correct and manually place them into the required cells. Do not copy and paste them from the existing workbook.

Create a new Monthly Production Analysis sheet set out the same as the original including the charts and Totals section. Do not copy and paste from the original workbook. Do not use any merged cells.

Create new Support worksheets as per your original workbook. No data is required in these. Simply creating them will suffice.

You have quite a bit of homework to do here Ganesh! Take your time with it and do it correctly as per the above instructions. There is, of course, a purpose to all this as I'm sure that you now understand.

Cheerio,

vcoolio.

Dear Sir,

Thank you very much for patience for my worksheet.

1) Fill in all columns with data. Do not leave blank cells and ensure that any formulae refer to the same row

For item (1) : Please not that I want to enter the data manually without formulas in the entire row. later after I try to put the formulas( as per you suggestion) if its success otherwise i can enter manually.

2) For item (2) : I agreed to remove all the quotation marks from all the formulae. for now i can manually enter the numeric values based on shift . leave it for me.. i can try later.

3) For item(3) : As you suggested, I was modified the dates as you want it.

4) For item(4) : I have unmerged all merging cells. if I forget, please consider my apology. 

Please continue to apply the code on the sample -MPA sheet (file) as attached link. You can make the changes whatever the code needs.. because we don't have any specific format to generate monthly production report.

Few points want share you:

1) Everyday one operator can work on 1 or 2 machines & one or more jobs of the different customers. so that we can give same date on multiple rows. the code will access the data of multiple rows with date( this is crucial point)
2) if the worker works on A, B,C & General, we can give 8 as actual hours and if the worker works on A&B and B&C , we can give 11 hours as actual hours
3) Also we are selecting the downtime code in the column R, automatically we can get the remarks column values using support sheet.
4) Please amend the as you want it.

Finally, You can do the sheet in your style, I don't have any objection. I can adjust my data as per your final output.

Sorry for inconvenienced caused to you again. 

Link : https://we.tl/t-W3rwppaG1f

Regards,

Ganesh

Hello Ganesh,

I've attached your file with some minor changes implemented.

You can find the file at this link.

Select an Operator ID in cell I3 and click on the button to collate the data for the selected Operator in the Monthly Production Analysis sheet.

You'll also note:-

- I've changed the set out of the source sheets slightly. Column A is shown as empty but it is used as a temporary helper column to hold the relevant worksheet names which you see on the MPA sheet with each data transfer.

- In Column U of all the source sheets, there is an XLOOKUP formula which selects the relevant remarks (from the Support Sheet) based on the code entered in Column S (A1001, A1002 etc...). The formula is as follows:-

=IF($S5="","",IFERROR(XLOOKUP($S5,'Support Sheet'!$B$2:$B$11,'Support Sheet'!$A$2:$A$11),""))

- In the Monthly Production Analysis sheet, you'll notice in the Totals section which feeds your charts, there are SUM formulae which total as the information is placed in the rows above when the button is clicked on. The charts will automatically adjust to suit the selected Operator. I've placed some random numbers in Columns O:R of all the source sheets just so you can see how the formulae work once data is transferred to the MPA sheet.

The VBA code that operates the data transfer is as follows:-

Option Explicit
Sub GaneshMPATest()
    
    Dim ws As Worksheet, wsMPA As Worksheet, lr As Long, sr As Long
    Set wsMPA = Sheets("Monthly Production Analysis")
    Dim OpName As String: OpName = wsMPA.Range("I3").Value
    
    Application.ScreenUpdating = False
    
        wsMPA.[A5].CurrentRegion.Offset(1).Clear
        
        For Each ws In Worksheets
        ws.Columns.Hidden = False
              If ws.Name <> "Monthly Production Analysis" And ws.Name <> "Support Sheet" Then
              lr = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
              ws.Range("A5:A" & lr) = ws.Name
                    With ws.Range("D4:D" & lr)
                            .AutoFilter 1, OpName
                            On Error Resume Next
                            Union(ws.Range("A5:A" & lr), ws.Range("C5:V" & lr)).SpecialCells(12).Copy
                            wsMPA.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                            On Error GoTo 0
                            .AutoFilter
                            ws.Columns(1).Clear
                    End With
              End If
        Next ws
    
    wsMPA.Columns.AutoFit
    Application.CutCopyMode = False
    Application.Goto wsMPA.[A1]
    Application.ScreenUpdating = True

End Sub

Hopefully, this is what you had intended how the workbook would operate.

Cheerio,

vcoolio.

Dear vcoolio,

Sorry for the late reply.

Actually the code works good. but the only thing when I apply  to my sheet is : I am always need to click on Run button to get the result.. please once check attached we transfer link.

Also

1) I want to hide the columns of I :M ( I to M)  in the MPR sheet.. lets say if I try to change the operator name and click on generate report, the hidden columns comes again to visible but I don't want them those columns (I to M) .

2) I don't want operator name in every row in MPA sheet as well . so that i can put it as hidden

2) when I select the operator  and click on Generate report button,  The Total section is going to be hidden . so that I am always need to be write the formula. 

let me one thing: lets say if I want to change the file name, is it required to amend in the code. because I want to make the file from 1st day of month to month end as Daily production report and after that I want to rename it as Monthly Production Report - Monthname , Year. ( i.e. I can put this Monthly production analysis in hidden mode)

is there any option to protect the code ?..if available let me know how to put it as not visible to see the code others

Please work on the attached sheet and clarify my doubt. it will be useful for me more.

Link: https://we.tl/t-YuEKH9ZYyG

Regards,

Ganesh

Hello Ganesh,

Place this amended code into a standard module, say Module1, and NOT the worksheet module:-

Option Explicit
Sub MachineShopMPATest()
    
    Dim ws As Worksheet, wsMPA As Worksheet, lr As Long, sr As Long
    Set wsMPA = Sheets("Monthly Production Analysis")
    Dim OpName As String: OpName = wsMPA.Range("H3").Value
    
    Application.ScreenUpdating = False
    
        wsMPA.[A5].CurrentRegion.Offset(1).Clear
        
        For Each ws In Worksheets
        ws.Columns.Hidden = False
              If ws.Name <> "Monthly Production Analysis" And ws.Name <> "Support Sheet" Then
              lr = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
              ws.Range("A5:A" & lr) = ws.Name
                    With ws.Range("D4:D" & lr)
                            .AutoFilter 1, OpName
                            On Error Resume Next
                            Union(ws.Range("A5:A" & lr), ws.Range("C5:V" & lr)).SpecialCells(12).Copy
                            wsMPA.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                            On Error GoTo 0
                            .AutoFilter
                            ws.Columns(1).Clear
                    End With
              End If
        Next ws
    
    wsMPA.Columns.AutoFit
    wsMPA.Columns("C").EntireColumn.Hidden = True
    wsMPA.Columns("I:M").EntireColumn.Hidden = True
    Application.CutCopyMode = False
    Application.Goto wsMPA.[A1]
    Application.ScreenUpdating = True

End Sub

then place this code into the Monthly Production Analysis sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("H3")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

MachineShopMPATest

Application.EnableEvents = True

End Sub

Thereafter, when you select an Operator from the drop down in H3, the codes will execute without you having to click on the button.

The code will also now hide Column C and Columns I:M in the MPA sheet when the code completes it's run.

2) when I select the operator  and click on Generate report button,  The Total section is going to be hidden . so that I am always need to be write the formula. 

Simply move it to the other side of the charts or below the charts. However, the SUM formulae are set into the Total section so, even if they are hidden, they will still calculate the totals required for the charts.

To password protect the codes, here is a little video which shows you the simple steps to do this:-

https://www.youtube.com/watch?v=hF47KOvpK68

let me one thing: lets say if I want to change the file name, is it required to amend in the code. because I want to make the file from 1st day of month to month end as Daily production report and after that I want to rename it as Monthly Production Report - Monthname , Year. ( i.e. I can put this Monthly production analysis in hidden mode)

I don't quite follow what you mean. Could you explain this further please. Do you want to create a whole new file(workbook) for each month and place the completed file(workbook) into a folder somewhere?

Cheerio,

vcoolio.

Dear vcoolio,

sorry for the late reply.

 let me one thing: lets say if I want to change the file name, is it required to amend in the code. because I want to make the file from 1st day of month to month end as Daily production report and after that I want to rename it as Monthly Production Report - Monthname , Year. ( i.e. I can put this Monthly production analysis in hidden mode) _ THIS IS NOT REQUIRED. 

Just give a suggestion or code that everyday production report is automatically copy into whole month production report (Another sheet). 

Once again you proved that you are superb and amazing because the suggested code and the theoretical process explanation is just amazing.. I am already generated all operators monthly production reports with a single selection.

Finally, I am ever grateful to you sir.

Regards,

Ganesh

Hello Ganesh,

Thank you for the kind words. That's very nice of you.

I understand what you mean now, I think!

So, at the end of each day, you basically just want to place the daily production record into a consolidated sheet as a permanent record (and close it off at the end of each year perhaps?).

Let me know if I'm on the right track with this and I'll then see what I can do for you.

Cheerio,

vcoolio.

Hello vcoolio Sir.

first of all,  I need to say thanks.. because you are always helped in a prompt manner.

I have raised another question (excel) in this forum. Kindly check once if possible and Give a solution because you are the only person to understand my English as well as my assumption and ideas.

Regards,

Ganesh