Consolidating Workbooks Using List

[Slicemahn]

Hi,
I have fifty sets of workbooks to combine. A set is two workbooks. Since the workbooks are in different folders, I thought I would just list the names of the workbooks in a sheet and loop through the names via VBA. I have attached a sample workbook with the code I am trying. What I need the code to do is to loop through all the workbook sets on Sheet 1 and combine and rename the file as LPReport Name.

Thanks in advance for your help.

Here is the code I am using


Sub Combine()
Dim DestWb As Workbook
Dim ws As Object
Dim curFile As String
Dim companionFile As String
Dim EndRow As Long
Dim RowStep As Integer
Const LBSummaryDirLoc As String = "C:\Users\user\Documents\LB"
Const LPRepDirLoc As String = "C:\Users\user\Documents\LP"
EndRow = Cells(1048576, 1).End(xlUp).Row
Application.ScreenUpdating = False
For RowStep = 2 To EndRow
Set DestWb = Workbooks.Add(xlWorksheet)
curFile = Dir(LPRepDirLoc & ActiveWorkbook.Cells(RowStep, 2).Value)
companionFile = Dir(LBSummaryDirLoc & ActiveWorkbook.Cells(RowStep, 1).Value)
Dim OrigWb As Workbook
Set OrigWb = Workbooks.Open(Filename:=curFile, ReadOnly:=True)
For Each ws In OrigWb.Sheets
ws.Copy After:=DestWb.Sheets(DestWb.Sheets.Count)
If OrigWb.Sheets.Count > 1 Then
DestWb.Sheets(DestWb.Sheets.Count).Name = curFile & ws.Index
Else
DestWb.Sheets(DestWb.Sheets.Count).Name = curFile
End If
Next
OrigWb.Close SaveChanges:=False
curFile = LPRepDirLoc
Dim NextWb As Workbook
Set NextWb = Workbooks.Open(Filename:=companionFile, ReadOnly:=True)
For Each ws In NextWb.Sheets
ws.Copy After:=DestWb.Sheets(DestWb.Sheets.Count)
If NextWb.Sheets.Count > 1 Then
DestWb.Sheets(DestWb.Sheets.Count).Name = companionFile & ws.Index
Else
DestWb.Sheets(DestWb.Sheets.Count).Name = companionFile
End If
Next
NextWb.Close SaveChanges:=False
companionFile = LBSummaryDirLoc
Next RowStep
Application.DisplayAlerts = False
DestWb.Sheets(1).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set DestWb = Nothing
End Sub