Consolidation of a range in different sheets to a single sheet

Dear All,

Hoping doing good!!!!

My Requirement is to consolidate a range (a row starts with specifications of ***** to "Remarks:") from 150 sheets to a single sheet

Here challenges are:

1. I dont know where did this range located in all sheets. So I want VB to search for "Specifications of" word & "Remarks" word and want to retrieve the related data from the next columns

2. Next columns might have hidden but still VB has to retrieve the data from the respective columns

3. This range can be there multiple times in a sheet, I even want this range also.

Did you already make a start on this?

what code do you have right now?

Dear GBeats,

I dont know VB coding. But I've recorded a macro for which I request you to have VB Code for me. I've selected the range in first sheet manually in this recorded macro.

Below VB Code is for first sheet, Now further VB Code must check range from "Specification Details of ****" to "Remarks" for the next sheets and has to add the same to the pivot table which I created in the macro. As I've 150 sheets to consolidate, So if number of rows exceeded in the first sheet, then it has to create and then do the same as per the below macro

Sub Consolidation()
'
' Consolidation Macro
' This is to consolidate a range with Pivot Wizard
'

'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array(Array("'56"" VIVA MS MICRA,BAHRAIN'!R1C1:R25C2", "Item1"), Array( _
"'56"" VIVA MS MICRA,BAHRAIN'!R28C1:R53C3", "Item2")), Version:= _
xlPivotTableVersion12).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").DataPivotField.PivotItems( _
"Count of Value").Position = 1
Range("A5").Select
With ActiveSheet.PivotTables("PivotTable3")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Column").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Value")
.Orientation = xlRowField
.Position = 2
End With
Range("A6").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("Row").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
End Sub