VBA Open Workbooks Based on Cell Values

[boone2021]

I need to open 2 workbooks based on the values in columns A and B and iterate until there are none left. I need it to be dynamic and loop.

The goal is to copy the sheet from the file name in Column B and post it to a named sheet in the workbook from Column C. Then close both workbooks and do it again for the next row

Here is what I have so far:
Sub OpenCopyPaste ()

Workbooks.Open "C:\Users\boone\Documents\Fast Track\Summary Reports\"


Workbooks.Open "C:\Users\boone\Documents\Fast Track\Weekly Files\"


'Need to put in dynamic workbook. Maybe Active??
Workbooks("Column B.xlsx").Worksheets("Sheet").Range("A8:R48").Copy _
Workbooks("Column C.xlsx").Worksheets("Weekly Data").Range("A1")

'Need to make dynamic
Workbooks("Column B.xlsx").Close SaveChanges:=False

'Now copy a range from workbook in column c to the new sheet that was copied
Sheets("2020 FT Tracking").Range("B23:B29").Copy Destination:=Sheets("Weekly Data").Range("T3")

Application.CutCopyMode = False

End Sub

 

[boone2021]

Okay I got to the point where both workbooks are opening correctly, but now my copy and past code isn't working with the dynamic WbSummary and WbWeekly names... Anyone know what's wrong?

Sub OpenWorkbook()

'Open the summary tracking and the assoicated weekly report. How to do that?? Loop

Dim WbSummary As String
Dim WbWeekly As String

r = 2
Do Until IsEmpty(Cells(r, 2)) And IsEmpty(Cells(r, 3))

WbSummary = Cells(r, 3)
WbWeekly = Cells(r, 2)


Workbooks.Open "C:\Users\Bjellis\Documents\Fast Track\Summary Reports\" & WbSummary & ""

Workbooks.Open "C:\Users\Bjellis\Documents\Fast Track\Weekly Files\" & WbWeekly & ""


'Need to put in dynamic workbook.
Workbooks("WbWeekly").Worksheets("Sheet").Range("A8:R48").Copy _
Workbooks("WbSummary").Worksheets("Weekly Data").Range("A1")

'Need to make dynamic
Workbooks("WbWeekly.xlsx").Close SaveChanges:=False

Sheets("2020 FT Tracking").Range("B23:B29").Copy Destination:=Sheets("Weekly Data").Range("T3")

Application.CutCopyMode = False

 

[boone2021]

Sub OpenWorkbook()

  'Open the summary  tracking and the assoicated weekly report. How to do that?? Loop

Dim WbSummary As String
Dim WbWeekly As String

r = 2
Do Until IsEmpty(Cells(r, 2)) And IsEmpty(Cells(r, 3))

WbSummary = Cells(r, 3)
WbWeekly = Cells(r, 2)


Workbooks.Open "C:\Users\Bjellis\Documents\Fast Track\Summary Reports\" & WbSummary & ""

Workbooks.Open "C:\Users\Bjellis\Documents\Fast Track\Weekly Files\" & WbWeekly & ""


 'Need to put in dynamic workbook. Maybe Active??
 Workbooks("WbWeekly").Worksheets("Sheet").Range("A8:R48").Copy _
    Workbooks("WbSummary").Worksheets("Weekly Data").Range("A1")

'Need to make dynamic
Workbooks("WbWeekly.xlsx").Close SaveChanges:=False

  
  'Close method has additional parameters
  'Workbooks.Close(SaveChanges, Filename, RouteWorkbook)
  'Help page: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.close

Sheets("2020 FT Tracking").Range("B23:B29").Copy Destination:=Sheets("Weekly Data").Range("T3")

Application.CutCopyMode = False