Excel VBA Code - Dynamic Workbook Sheet Name Reference from Cell Output

Question

Greetings!

I have existing VBA Code, as follows, in my workbook, however, if possible, would like to optimize the code so that the source workbook sheet reference, which is currently "Comp", is instead a dynamic reference to a cell output in my destination workbook (the dynamic output cell in the destination workbook I wish to reference is named "CompSetNo").

To the extent additional detail is helpful, essentially, there will only ever be three possible variations of the sheet name "Comp" in the source workbook. If the end user selects competitive set number "1" from the dropdown list, then the name of desired source workbook sheet is "Comp"; if the end user selects competitive set number "2" from the dropdown list, then the name of the desired source workbook sheet is "Comp_2"; and, if the end user selects competitive set number "3" from the dropdown list, then the name of the desired source workbook sheet is "Comp_3".

Any assistance would be greatly appreciated. The code currently in use is as follows below:

Private Sub CommandButton3_Click()

Dim Fname As String

Dim SrcWbk As Workbook

Dim DestWbk As Workbook

Set DestWbk = ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")

If Fname = "False" Then Exit Sub

Set SrcWbk = Workbooks.Open(Fname)

SrcWbk.Sheets**("Comp")**.Range("A:AG").Copy DestWbk.Sheets("STR Base Year").Range("A:AG")

SrcWbk.Close True

MsgBox "STAR Base Year import completed. Please proceed to next."

End Sub

Answer 1

Is CommandButton3 an ActiveX button on a worksheet? If so, you could set the value of a cell on that sheet to the worksheet name based on the value selected in the drop down list. Then use Range(...) instead of "Comp" in your code.

Or is it a button on a userform? If so, you can derive the worksheet name from the value of the combo box and use it instead of "Comp".