How can i reffer sheet by active cell value from other workbook-CodePudding

I need to write macro that will open one workbook (lets call it 2) and by cell value in other workbook (1) will open specyfic sheet in workbook(2). Cell value in (1) is the same as sheet name in (2). Then it will need to copy some thing but i can handle with that.

So my question is: there is any option to reference sheet name by activecell value in other workbook?

also if his can be problem. both of this excels will be on network drive

CodePudding user response：

To open the workbook, you need its path.

path = "C:\....\Workbook2.xlsx" 'Example

Set wb2 = Workbooks.Open(path)

Get the sheetname from Workbook 1.

sheetname = wb1.Worksheets(1).Range("A1").Value2 
'Suppose cell value of cell A1 in workbook 1 is the sheet name.

Set ws2 = wb2.Worksheets(sheetname)
'ws2 is the worksheet you want...
ws2.Activate  'Example method of ws2.

Both workbooks can be on a network. As long as you are connected to the network, it will work. Path will change accordingly.