reference sheet in another workbook by using the indirect function

Question

I am looking for a solution for the correct syntax to use.

I have extracted the sheet names from another work book and put in to a worksheet in the current workbook I am working on.

I want to use the cell value as a reference to copy and paste the sheet into my current workbook.

    Workbooks.Open Filename:= _
     "D:\Projects\ASE Templates\ASE Template White Book.xlsx"
      Application.Left = 192.25
        Application.Top = 1
        Sheets("Indirect('[ASE RTU Addressing with Automation.xlsm]' & 
                                  ""'Tab Names from white book'"" & ""!A1"")).Select"
        Sheets("2S0 P143 MAIN BUS SECTION").Copy After:=Workbooks( _
           "ASE RTU Addressing with Automation.xlsm").Sheets(4)

The first section of the select is how i am trying to incorporate the indirect function into sheet (REF) the next line works as it uses the sheet(name)

the sheet names will never be the same which is why i have extracted before trying to run this code

jkpieterse jkpieterse · Accepted Answer · 2018-01-22T13:52:12

Suppose your sheet names are in cells A1:A10:

Dim oCell As Range
Workbooks.Open Filename:= _
 "D:\Projects\ASE Templates\ASE Template White Book.xlsx"
For Each oCell In ThisWorkbook.Sheets("Tab Names from white book").Range("A1:A10")
    Sheets(oCell.Value).Copy After:=Workbooks( _
       "ASE RTU Addressing with Automation.xlsm").Sheets(4)
Next

reference sheet in another workbook by using the indirect function

1 Answers