Ask Experts Questions for FREE Help !
Ask
    vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #1

    Jan 22, 2012, 04:08 AM
    Collating information available on excel sheets
    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
    Attached Files
  1. File Type: zip Trialsheet for askmehelp.zip (19.0 KB, 47 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    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.
    Attached Files
  3. File Type: zip Trialsheet for askmehelp.zip (26.3 KB, 54 views)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to connect two excel sheets? [ 3 Answers ]

Hi Friends, I want to connect in such a way that whatever I will copy in source file should be copied automatically in target file. It would be great to get help from you.

How to interlink sheets in excel? [ 0 Answers ]

I want two sheets to connect so that when I add another row a new row would be added in both sheets with the new information. How do I do that? Basically, I have an attendance sheet that I would like to be auto filled whenever I add a new student to our my excel data base.

Link of excel sheets [ 1 Answers ]

Link excel speadsheet

Excel Sheets Connection! [ 3 Answers ]

Dear All, (Sorry my question is not about word processor) I want to ask that how can be the data added automaticall to from one excel sheet to another. For e.g I have one excel sheet by the name ex1.xls and 2nd is ex2.xls. Let suppose I want to add something in ex1.xls and want the same addition...

Excel Budget Sheets [ 2 Answers ]

I am preparing for an job exam in Excel and would like to practice building budget sheets using formulas. Does any one now of any website out there were I can find samples of budgets sheets to build off? i.e Sums, then averages within sums> Thank You


View more questions Search