VBA Open Workbooks Based on Cell Values

boone2021

New Member
Joined
Dec 30, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

boone2021

New Member
Joined
Dec 30, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 30, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,368
Messages
5,635,856
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top