Macro to generate a report in defined format

[Chandra Deep Yadav]

Hi forum,

I want to have a macro which can generate a report based on available set of data.

I have attached an excel spreadsheet for the reference, in which Summary sheet is the inputs, from which we need to enlist all the controllers with respective MSSB at CPQ sheet in the defined format, with their respective quantity.

The number of MSSBs will be variable.

Thanks in advance.

 

[p45cal]

Sub blah()
With Sheets("Summary")
  For Each cll In Intersect(.Range("A5").CurrentRegion, .Range("N5:AD" & Rows.Count))
  If cll.Value > 0 Then
  Set Destn = Sheets("CPQ").Cells(Rows.Count, "A").End(xlUp).Offset(1)
  Destn.Resize(, 4).Value = Array("Sample", .Cells(cll.Row, 2).Value, .Cells(3, cll.Column).Value, cll.Value)
  End If
  Next cll
End With
End Sub

 

[Chandra Deep Yadav]

Thanks a lot p45cal!!!