Mr. Zeddy,
I'm very new to macro, but is loving it. The only macro I know how to do is recording macro. Please help me correct this recorded code, in a way where I can execute using looping statement (term?) I have multiple hyperlink files (500 files) exported data from SharePoint into one worksheet, within each file I like to extract data from a merge cell column (B1:E1) data rows 1:21, each file has the same set of label in A1-A17. I like to extract the data mentioned.

my recorded macro code I stopped after the 3rd file:

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveCell.Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.CutCopyMode = False
Workbooks.Open Filename:= _
"https://sp.acs-inc.com/ghs/mmis/MT/MTMMIS/Team%20Working%20Files/SIT_Test_Scripts/MT_SIT_TCs/Base_TCs/Benefit_Plan/BP_Rvw/BP_001/UC-B-PG6-BEN-0001-A1001-LOBSrcAlFlds.xls"
ActiveWindow.Visible = False
Windows("UC-B-PG6-BEN-0001-A1001-LOBSrcAlFlds.xls").Visible = True
ActiveCell.Offset(-14, 0).Range("A117").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Windows("BP Peer Review_01182013.xlsx").Activate
Sheets("Sheet3").Select
ActiveCell.Offset(-1, -5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveCell.Offset(6, 1).Range("A1").Select
Sheets("owssvr(3)").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.CutCopyMode = False
Workbooks.Open Filename:= _
"https://sp.acs-inc.com/ghs/mmis/MT/MTMMIS/Team%20Working%20Files/SIT_Test_Scripts/MT_SIT_TCs/Base_TCs/Benefit_Plan/BP_Rvw/BP_001/UC-B-PG6-BEN-0001-A1002-LOBSrcEntrNoFlds.xls"
ActiveWindow.Visible = False
Windows("UC-B-PG6-BEN-0001-A1002-LOBSrcEntrNoFlds.xls").Visible = True
ActiveCell.Offset(-21, -2).Range("A121").Select
Selection.Copy
Windows("BP Peer Review_01182013.xlsx").Activate
Sheets("Sheet3").Select
ActiveCell.Offset(-5, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("owssvr(3)").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:= _
"https://sp.acs-inc.com/ghs/mmis/MT/MTMMIS/Team%20Working%20Files/SIT_Test_Scripts/MT_SIT_TCs/Base_TCs/Benefit_Plan/BP_Rvw/BP_001/UC-B-PG6-BEN-0001-A1003-LOBSrcEntrOneFldMRes.xls"
ActiveWindow.Visible = False
Windows("UC-B-PG6-BEN-0001-A1003-LOBSrcEntrOneFldMRes.xls").Visible = True
ActiveCell.Offset(-8, 0).Range("A121").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BP Peer Review_01182013.xlsx").Activate
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveCell.Select
End Sub