Check out some similar questions!

vks64 · Jan 22, 2012, 04:08 AM

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

JBeaucaire · Jan 23, 2012, 12:59 PM

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:

Code:

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.