CODEWITHSUNDEEP
excelvbavariables
Jordan Shep Shepherd
Jordan Shep Shepherd

Reputation: 43

Set Worksheet Name Equal To A Variable

I want to be able to open up a second data source (workbook) and select the sheet within the second workbook which is equal to the name in a cell in my first workbook.

So far I have:

    Dim src As Workbook
    Dim src2 As Workbook
    Dim shtno As String
    Dim ws As Worksheet

    Set src = ActiveWorkbook
    shtno = Sheets("CONTROL").Range("G3").Value
    Set ws = Sheets(shtno)

    Workbooks.Open Filename:= 'file link
    Set src2 = ActiveWorkbook

    ws.Select

Where 'ws.Select' should select my sheet required. Where am I going wrong?

Thanks.

Upvotes: 0

Views: 577

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

BigBen is correct.

You need to move the Set ws statement down below the Workbooks.Open statement then:

Set ws = src2.Sheets(shtno)
ws.Select

NOTE:

  • once you Open the new workbook, it becomes the Active workbook
  • you can only Select a worksheet on the Active workbook

Upvotes: 1

Related Questions