How do you to refer to an specific worksheet depending on a selected value?

[dpa]

Hi. How do you to refer to an specific worksheet depending on a selected value.

For example, I have 4 sheets named: "5", "ac", "ad", "6" (instead of "sheet1", "sheet2", "sheet3", "sheet4"), in the vba code, the user may choose from three options: A or B or C, so:

if the user selects A, I want a variable "select" to have a .value of 5
if the user selects B, I want a variable "select" to have a .value of ac
if the user selects C, I want a variable "select" to have a .value of ad

I tried something like this:

If selection = A then select = 5
If selection = B then select = ac
If selection = C then select = ad

worksheet("6").range(A1:A10).value = worksheet("select").range(A1:A10).value

Therefore, depending on the decision of the user, the values copied are from any of the worksheets 5,ac or ad into worksheet 6
but I am not sure hot to refer this value: worksheet("select").range(A1:A10).value
or how do I let the code know that the "select" value is the nam e of the sheet...?

 

[jbeaucaire]

Don't use VBA variables that are named after other VBA functions or worksheet function. So "select" is out. In this sample code, I just selected B2 at random...

Dim ws as Worksheet

Select Case Range("B2").Value
   Case "A":   Set ws = Sheets("5")
   Case "B":   Set ws = Sheets("ac")
   Case "C":   Set ws = Sheets("ad")
End Select

Sheets("6").Range("A1:A10").Value =  ws.Range("A1:A10").Value