Production Report to Itemize Each Part Number with Summary

[Billy Hill]

I have a production report that has the goals and total units made for 3 shifts. I summarize each part number for easy reading with the totals. The results can be an image or data.

My source data looks like this:

And I want the results to look like this with all the part numbers, (an image of the summaries is fine, I will be emailing it to the owner for review)

Is there a way to automate this in VBA?

TYIA!

~B

 

[DanteAmor]

You have the macro in the workbook "PERSONAL.XLSB", so thisWorkbook refers to the workbook "PERSONAL.XLSB".

To refer to another workbook you must put the name of the other workbook, for example:

Try updating the following lines in your code:

Sub CreateSummaryTable()
  Dim wsSource As Worksheet
  Dim wsSummary As Worksheet
  Dim partNumber As String
  Dim lastRow As Long
  Dim rngPartData As Range
  Dim rngSummary As Range
  Dim chartObj As ChartObject
  
  Dim wb2 As Workbook
  Set wb2 = Workbooks("SHOP PRODUCTION ANALYSIS.xlsx")
  'Set the source worksheet
  Set wsSource = wb2.Sheets("DAILY PRODUCTION REPORT")

  'Create a new worksheet for the summary tables
  Set wsSummary = wb2.Sheets.Add(After:=wsSource)
  wsSummary.Name = "Summary"
  '
  '....

End Sub