VBA - Extract Cell H25 from every Sheet

[Sunvisor]

I have hundreds of sheets in a workbook.

I need to extract each of the sheets names and then each of the sheets value of cell H25 then paste them starting from A1-B1 all the way till the end.

Any suggestions!?

 

[Domenic]

Assuming that the workbook containing the data is the active workbook, the following macro displays the list in a newly created worksheet within the active workbook...

[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=darkblue]Sub[/COLOR] test()[/FONT]
 
[FONT=Verdana]  [COLOR=darkblue]Dim[/COLOR] MyArray() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR][/FONT]
[FONT=Verdana]  [COLOR=darkblue]Dim[/COLOR] wkbSource [COLOR=darkblue]As[/COLOR] Workbook[/FONT]
[FONT=Verdana]  [COLOR=darkblue]Dim[/COLOR] wksDest [COLOR=darkblue]As[/COLOR] Worksheet[/FONT]
[FONT=Verdana]  [COLOR=darkblue]Dim[/COLOR] wks [COLOR=darkblue]As[/COLOR] Worksheet[/FONT]
[FONT=Verdana]  [COLOR=darkblue]Dim[/COLOR] NumShts [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT=Verdana]  [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
 
[FONT=Verdana]  [COLOR=darkblue]Set[/COLOR] wkbSource = ActiveWorkbook[/FONT]
 
[FONT=Verdana]  NumShts = wkbSource.Worksheets.Count[/FONT]
 
[FONT=Verdana]  [COLOR=darkblue]ReDim[/COLOR] MyArray(1 [COLOR=darkblue]To[/COLOR] NumShts, 1 [COLOR=darkblue]To[/COLOR] 2)[/FONT]
 
[FONT=Verdana]  [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] wks [COLOR=darkblue]In[/COLOR] wkbSource.Worksheets[/FONT]
[FONT=Verdana]      Cnt = Cnt + 1[/FONT]
[FONT=Verdana]      MyArray(Cnt, 1) = wks.Name[/FONT]
[FONT=Verdana]      MyArray(Cnt, 2) = wks.Range("H25").Value[/FONT]
[FONT=Verdana]  [COLOR=darkblue]Next[/COLOR] wks[/FONT]
 
[FONT=Verdana]  [COLOR=darkblue]Set[/COLOR] wksDest = wkbSource.Worksheets.Add[/FONT]
 
[FONT=Verdana]  wksDest.Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)).Value = MyArray[/FONT]
 
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]