Hello sir,
I have some excel sheets (15 to 30 nos) containing identical data on certain x number of items; but relates to different dates. Now I need to collate the data pertaining to particular one item(among x number of items)(which is available in 15 to 30 worksheets)and consolidate/group all of them into a single excel sheet.The result must appear like this:
The resultant excel sheet must have all the rows relating to the selected item extracted from all the sheets and presented chronologically in a single sheet.
I have also attached a simpler sample worksheet on the above model with expected answer sheet affixed on a worksheet
Thanks in Advance
Vks64

I have changed the name of that sheet to the name of the stock you want to collect. I then added this macro into the sheet module of that sheet:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, vFIND As Range

Me.UsedRange.Offset(1).EntireRow.ClearContents
On Error Resume Next

For Each ws In Worksheets
If IsDate(ws.Name) Then
With ws
Set vFIND = ws.Range("A:A").Find(Me.Name, LookIn:=xlValues, LookAt:=xlWhole)
If Not vFIND Is Nothing Then
vFIND.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
Set vFIND = Nothing
End If
End With
End If
Next ws

Columns.AutoFit
Range("A1").CurrentRegion.Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlYes

End Sub


That macro collects data from any worksheet that has a date for a sheetname.

This macro activates itself anytime you bring that sheet up onscreen. You can COPY this sheet and rename it to another stock, that sheet would then collect the data for that stock.