Console Multiple Sheet data into one single sheet on specific headers only. RRS feed

  • Question

  • Hey Team

    I am looking to consolidate multiple sheets data into one single sheet on specific headers below data only.

    Specific headers data comes any column in the cell (Example like headers "Sale Price" , "Mutual Price"  it will come anywhere "C" or "E" or 'F" Column)

    However, need to pick each individual header on below data to console into one single sheet as its huge line items.

    Please help to get the VBA Code?



    Wednesday, June 5, 2013 7:16 AM

All replies


    Possible something like this...

    Sub FindEachSpecialColumn()
     Dim vNames As Variant
     Dim M As Long
     Dim N As Long

     vNames = Array("Sale Price", "Mutual Price", "Gross", "Larry", "Moe", "Curly")
     For M = LBound(vNames) To UBound(vNames)
       For N = 1 To Columns.Count
         If Columns(N).Cells(1, 1).Value = vNames(M) Then
           'do something with column contents
            Exit For
         End If
       Next 'N
     Next 'M
    End Sub

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:35 PM
    Thursday, June 6, 2013 12:37 AM
  • It should not help me..


    below is the excel sheet

    Date        Number          Product     Master      Amount       Worth    Mix

    23/04/12 34                  Cat            Group       13000          2                30

    23/05/12 36                  Mat            Group       11000          1                 32

    23/06/13 40                  SUIT          Group1       8000          5                 43


    what i need i wanted to go each excel sheet to filter these header below data only (Date, Number, Product, Amount) and copy and paste in one console sheet.

    I have multiple sheet in one worksheet/multiple worksheet, go to each tab to pick these particular header below data's, copy it and paste into one console sheet.



    Monday, June 17, 2013 5:14 PM
  • It should not help me...

    Assume below is the excel sheet.

    Date Number Product Time  Amount Worth Contain
    23/10/12 2222  Biscuit 22 2300 2  Group
    23/10/12 2400  Masu 23 1400 1  Wint
    24/03/13 2111  Mins 11 1100 1  Wint2
    26/03/13 2222  Mins 11 900 1  Wint2

    I wanted to go each each sheet to filter these particular header data (Date,Number,Product,Contain,Amount) only copy it and paste into one console sheet do to manually.

    however what i need that i have multiple excel sheet data into one workbook/multiple worksheet to each sheet, go to each tab select these specified header below data (Date,Number,Product,Contain,Amount) only copy it and paste into one console sheet.

    Please suggest the VBA Code that solve my issue



    Monday, June 17, 2013 5:27 PM
  • someone please look into this issue...
    Monday, June 24, 2013 5:44 PM
  • Try manually recording a macro of you consolidating the data (Data, Consolidate. the manipulating the resulting code. text columns don't get consolidated, but easy to add extra formulae.

    Try clicking the create links option and use top row.

    Once the sheets are consolidated, hide the columns you don't want.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, June 24, 2013 10:26 PM
  • Thanks for your response,

    we can find each sheet the header but column not fixed (i.e as the data comes any column) that's i wanted to go each sheet find the header below data and paste into one single sheet.

    please help it.

    Tuesday, June 25, 2013 8:45 PM
  • Try the manual consolidation, it should handle different column headers, it only adds values with same headers.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, July 1, 2013 9:26 PM