VBA to open workbook from cell reference

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
Code:
Sub Open_Workbook()
    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
      Dim LocationName As String
     LocationName = Range("'A'!F8").Value
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Height\" & LocationName"
    Set destWB = ActiveWorkbook
    
'   Find last row of Sieve data in destination workbook
    lastRow = destWB.Sheets("B6").Cells(Rows.Count, "A").End(xlUp).Row + 1
'   Copy Mold Heights data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G3").Copy
    destWB.Sheets("B6").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("E41").Copy
    destWB.Sheets("B6").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D18").Copy
    destWB.Sheets("B6").Range("C" & lastRow).PasteSpecial xlPasteValues
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True

That's the code that I have inside of a macro that's significantly bigger. I'm trying to open up a workbook from a cell reference. I need it to open up
Code:
C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Height\" & LocationName"
and the cell reference for the location name is F8 of the source workbook.
Let's say cell f8 says NP or SP
C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Height\NP.xlsx
or C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Height\SP.xlsx
is what I want it to open
My destWB.sheets is a name that's referenced in B6 of my source workbook and I don't know how to set that up either
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You'll want to use the following:
Code:
Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Mold Height\" & LocationName & ".xlsx"
and
Code:
destWB.Sheets(srcWB.Sheets("A").Range("B6")).Range...
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top