Open Specific Sheet From a Collection of Workbooks

JDG1992

New Member
Joined
Jun 11, 2015
Messages
2
Currently stumped with a project I'm working on. I have a worksheet with a column of uniquely named security IDs, and a column next to it with a name of a corresponding workbooks. That other workbooks listed have a table containing some of those securities in column a, and in column b is the name of a tab which contains information on cash flows from those securities. In all there are 8 separate workbooks containing the cashflow information.

I would like to automate the process of searching for the cashflow information. Is there a way to open the specific sheet using the security ID as a catalyst? I was trying to think of a way to run a macro where it would open the sheet using the sheet value from an iferror(vlookup...) string to cycle through the workbooks. I'm unsure how to perform the open sheet function and am unsure if the iferror vlookup search is the most efficient way to do this. Any advice is much appreciated.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Paste this in a standard module in VBA. Put your cursor on a cell with the security ID. Run the macro. This assumes that the Security ID is the same as the sheet name. If not then we will have to change the code a little.

Code:
Sub OpenSecurityIDSheet()    
  Dim Sht As Worksheet
  Dim A As String
  
  A = ActiveCell.Text
  
  For Each Sht In ActiveWorkbook.Sheets
    If Sht.Name = A Then
      Sht.Activate
      Exit For
    End If
  Next Sht


End Sub
 
Upvote 0
Unfortunately, it is not the same sheet name. Also, the sheet is not located in the active workbook, rather it is located in a separate collection of workbooks.

Currently, these are the steps I take. I have to look up the security and find it's corresponding workwook in another file. Then, I open that workbook, find the security name, and find the name of the sheet that corresponds with it. In a perfect world I would be able to not have to look up the workbook that houses the cash flows through the "look up" workbook and access the sheet right away from the original workbook.
 
Upvote 0
Maybe you could simplify things. There are ways to open other workbooks and go find a sheet. For the macro to open another workbook(wb) and find the sheet, you would need to provide that information in cells, or I could create a macro where you select the file open.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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