0

I am trying to select a sheet name in workbook1 called exactly as the cell's value ("K2") in Workbook2 and in a sheet called "GEO_MSCI_Indexes".

The idea is to have a dynamic reference to a worksheet in another workbook.

    Sub zzz()

Dim workbook2 As Workbook
Set workbook2 = Workbooks("Fondi non optica.xlsm")

Dim workbook1 As Workbook
Set workbook1 = Workbooks("MSCI Regional and Country Weights by Market Cap updated BD 2.xls")



workbook1.Sheets(Array(workbook2.Sheets("GEO_MSCI_Indexes").Range("K2").Value)).Select

        

End Sub
fonzelv
  • 55
  • 1
  • 5
  • 1
    What is the value in cell K2? – BigBen Mar 22 '22 at 17:20
  • Removing the Array would probably work assuming the value of K2 has the Worksheet name only. No idea why an Array was added here. – Ricardo A Mar 22 '22 at 17:24
  • The value in K2 is December – fonzelv Mar 22 '22 at 17:44
  • So are you attempting to select a Sheet named "December" ? if so then Remove Array( ) from within Sheets in the last line. – Ricardo A Mar 22 '22 at 17:47
  • 2
    You could lose the `Array(...)` part but the problem is probably that you have to make sure that the correct workbook is active: use `workbook1.Activate` before selecting the worksheet. But the question is why would you even want to select or activate. Take a look at [this legendary post](https://stackoverflow.com/a/10717999) on how to avoid using `Select`. – VBasic2008 Mar 22 '22 at 18:10
  • 2
    `Array()` is not the issue here. You haven't told us what the problem is though - what does your code do instead of work? – Tim Williams Mar 22 '22 at 18:10
  • It simply does not work and returns the following error: Run-time error '1004': Select method of Sheets class failed – fonzelv Mar 23 '22 at 11:12
  • 1
    You can't select a sheet if it is hidden (or very hidden). Have you tried using `workbook1.Activate`? – VBasic2008 Mar 23 '22 at 11:15
  • It works, many thanks mate, but I did not quite the rationale behind it. Would you mind explaining? Thanks again! – fonzelv Mar 23 '22 at 13:47
  • 1
    I hope you mean the `Activate` part. You cannot select a worksheet if its workbook isn't active. In the same way, you cannot select a range (cell) if its worksheet is not active. That's how `Select` works. – VBasic2008 Mar 23 '22 at 14:08

0 Answers0