Extract specific fields and its values from multiple sheets to one summary sheet

[vbanewuser]

I'm trying to extract specific fields from multiple sheets in to one sheet. Each sheet has multiple rows with row heading as ID, name, total costs, total revenue. I need to find the rows that have these headings. Then the value for each one is listed in different columns - like ID value is listed in Column C, name in column C again, total costs in column G and total revenue in column J. Macro I've just copies and pastes all rows with these values. but I was looking for values to updated in columns set as ID, name, total costs, total revenue in summary sheet and pasting all values from multiple sheets to this sheet under these column headers.

Here is the sample of data in multiple sheets with total costs and total revenue in different row counts. Columns for values in each field is constant, so column B in below table will be used to extract ID and name details, Column to extract total costs and column I to extract total revenue value.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]123
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD]abcd
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cost 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]123
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cost 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3456
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Costs
[/TD]
[TD][/TD]
[TD][/TD]
[TD]100000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost adjustment

[/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total revenue
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300000
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here is what I am looking for in summary sheet:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD]Total Costs
[/TD]
[TD]Total Revenue
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]abcd
[/TD]
[TD]100000
[/TD]
[TD]300000
[/TD]
[/TR]
[TR]
[TD]456 (from next sheet)
[/TD]
[TD]efgh (from next sheet)
[/TD]
[TD]150000 (from next sheet)
[/TD]
[TD]400000 (from next sheet)
[/TD]
[/TR]
</tbody>[/TABLE]

Please help.

Thank you.

 

[jolivanes]

Maybe try this on a copy of your workbook.
Copies from all sheets except first (leftmost) sheet.
First (leftmost) sheet is called "summary".
Copied values will be pasted in first (leftmost) sheet.
Check all your spelling. Different spelling will error code. Change where required.

Sub Transfer_From_All_Sheets()
Dim i As Long, a
Sheets("summary").Range("A1:D1").Value = Array("ID", "Name", "Total Costs", "Total Revenue")
    For i = 2 To ActiveWorkbook.Sheets.Count
        a = Array( _
        Sheets(i).Range("A:A").Find("ID", , , 1).Offset(, 2).Value, _
        Sheets(i).Range("A:A").Find("Name", , , 1).Offset(, 2).Value, _
        Sheets(i).Range("A:A").Find("Total Costs", , , 1).Offset(, 6).Value, _
        Sheets(i).Range("A:A").Find("Total revenue", , , 1).Offset(, 9).Value)
        Sheets("summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4) = a
    Next i
End Sub

 

[vbanewuser]

Hi, I tried this code. It copies the column headers as ID, Name, Total costs, Total Revenue and then stops. Debug message shows "Object variable or With block variable not set". Would you know what it refers it? Thanks.

Maybe try this on a copy of your workbook.
Copies from all sheets except first (leftmost) sheet.
First (leftmost) sheet is called "summary".
Copied values will be pasted in first (leftmost) sheet.
Check all your spelling. Different spelling will error code. Change where required.

Sub Transfer_From_All_Sheets()
Dim i As Long, a
Sheets("summary").Range("A1:D1").Value = Array("ID", "Name", "Total Costs", "Total Revenue")
    For i = 2 To ActiveWorkbook.Sheets.Count
        a = Array( _
        Sheets(i).Range("A:A").Find("ID", , , 1).Offset(, 2).Value, _
        Sheets(i).Range("A:A").Find("Name", , , 1).Offset(, 2).Value, _
        Sheets(i).Range("A:A").Find("Total Costs", , , 1).Offset(, 6).Value, _
        Sheets(i).Range("A:A").Find("Total revenue", , , 1).Offset(, 9).Value)
        Sheets("summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4) = a
    Next i
End Sub

 

[jolivanes]

Do all the sheets, except the first sheet, have "ID", "Name", "Total Costs" and "Total revenue" in the first column (Column A)?
Is the spelling the same? Capitals or non capitals, leading or trailing spaces etc etc
In your post you mention "multiple sheets". Does that mean some sheets and not all?
If so, what are the restrictions? Which sheets are to be included or which sheets are to be excluded?
If all else fails, drop your workbook in "Dropbox" and give us the link to it.

 

[vbanewuser]

Yes, spelling same and capital, leading, trailing spaces are same.
I meant all sheets. Have created the "summary" as left most sheet.
All sheets have ID and Name in merged columns A through C and total costs/total revenue in merged columns A through D.
I tried changing range from "A:C" but did not work.

Do all the sheets, except the first sheet, have "ID", "Name", "Total Costs" and "Total revenue" in the first column (Column A)?
Is the spelling the same? Capitals or non capitals, leading or trailing spaces etc etc
In your post you mention "multiple sheets". Does that mean some sheets and not all?
If so, what are the restrictions? Which sheets are to be included or which sheets are to be excluded?
If all else fails, drop your workbook in "Dropbox" and give us the link to it.

 

[jolivanes]

What happens when you un-merge the cells? Then check if all the info to be copied is in the right columns.
BTW, merging is considered a problem in the making. If not really necessary, don't use it. Use "Center Across Selection" instead