VBA - Average Cycle times macro

[Kenn]

Hi again. I need a little help with a macro & would appreciate any suggestions.
In workbook 01, Column A consists of a very long list of part numbers, some of which may repeat. Column B contains the cycle times for processing each part. I would like to have a macro in Workbook 02 to search Workbook 01 to find the shortest cycle time, the longest cycle time & the average cycle time for each part.

Thank you
Kenn

 

[northwolves]

Some data as example is helpful to understand your need.

Maybe:

=MIN(B:B)
=MAX(B:B)
=AVERAGE(B:B)

Work for you.

 

[p45cal]

A pivot table created by a macro will be easiest to execute, but what version of Excel are you using?

 

[Kenn]

Thanks p45cal,

I would prefer to do this with VB. I have tried to record a macro to create a pivot table, but when you go to another workbook the macro doesn't record anything.

Regards
Kenn

 

[p45cal]

I would prefer to do this with VB. I have tried to record a macro to create a pivot table, but when you go to another workbook the macro doesn't record anything.

A macro is VB(A), you just have to make adjustments.
But you haven't answered my question:

what version of Excel are you using?

For example, the following macro should, with a few provisos, work in Excel 2003 (and possibly 2010):

Sub blah()
With Workbooks("[COLOR=#ff0000]Workbook01.xls[/COLOR]").Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
Set SD = Intersect(.Range("A1").CurrentRegion, .Range("A:B"))
End With
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=SD)
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PC, TableDestination:=Range("A3"))
With PT
  .AddFields RowFields:=Array(SD.Cells(1).Value, "Data")
For i = 1 To 3: .PivotFields(SD.Cells(1, 2).Value).Orientation = xlDataField: Next i
  .DataPivotField.Orientation = xlColumnField
  With .DataFields(1)
    .Caption = "Min"
    .Function = xlMin
    .NumberFormat = "0.00"
  End With
  With .DataFields(2)
    .Caption = "Max"
    .Function = xlMax
    .NumberFormat = "0.00"
  End With
  With .DataFields(3)
    .Caption = "Average"
    .Function = xlAverage
    .NumberFormat = "0.00"
  End With
  .ColumnGrand = False
  .RowGrand = False
End With
End Sub

The provisos are:
1. There's a workbook called Workbook01.xls and it's open. If not adjust the name of the workbook in the code.
2. The source data is on a sheet called Sheet1, if not adjust the sheet name in the macro.
3. The sourcedata starts in A1 of the above sheet, with headers in row 1. If not, we've some tweaking to do.
4. The code adds a pivot table to the active sheet of the active workbook at the time the code is run, so make sure where you want the results is active at the time you run the code, and it would be good if the sheet is empty at the start.

 

[Kenn]

Thank you P45cal.

I was able to get it to work by modifying your code.

Thank you
Kenn.