Adding Workbook Name in Column and Loop

[DPhilip]

Hi Everyone: Can someone please point to me in the right direction? My attempt is to go through a specific folder and consolidate all the files into one workbook.

I am having three setbacks.

• First one is I am not sure how to loop through all the workbooks.
• Second one is adding the file name in column A so I know which workbook it came from.
• Third is dynamically finding the Row labeled "Region" and copy below that. This row might be row 52 this month but it can change to row 45 next month..

This is what I have so far. Thank you in advance for any help.


Sub COPY_PASTE_FROM_BLUESHEET()
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Dim wb As Workbook
Dim WbTemp As Workbook
Dim WS As Worksheet
Dim WsTemp As Worksheet
Dim Lastrow As Long
Dim X As Variant
Sheets("Step 2").UsedRange.ClearContents
Set wb = Application.Workbooks.Open(Worksheets("Step 1").Range("B6"))
Set WS = wb.Sheets("Regional Estimates")
Set WbTemp = ThisWorkbook
Set WsTemp = WbTemp.Sheets("Step 2")
Lastrow = WsTemp.Range("B2")
'IF Cells.Find("Region Program").Offset(-4, 0)
WS.Range("B52:J500").Copy WsTemp.Range("B2")
ActiveWorkbook.Close


END SUB