VBA reference cell value to be worksheet name

[treehook]

Hello,

Sorry if this a basic question, I'm learning vba and have WORKSHEETNAME in cell A1, it is also the name of a tab in my workbook.

I'm not sure what the code is to get the value of cell A1 and make it into a worksheet variable in VBA that i can access.

any expertise is greatly appreciated. Thanks.

 

[Jonmo1]

Welcome to the board..

You'll also need to specify the name of the sheet that A1 is on that holds this worksheet name...
Assuming that is on a sheet named "Master"

Dim MySheet As String, ws As WorkSheet
MySheet = Sheets("Master").Range("A1").Value
Set ws = Sheets(MySheet)
'then refer to that worksheet like
ws.Range("A1:A10").Interior.Colorindex = 3


Hope that helps.

 

[treehook]

Sure did. Thank you so much for taking the time to help out!

 

[skyline7441]

Please help me to fix this code...

Function IVOLL(LECTURER As String) As String

If LECTURER = "" Then
IVOLL = ""

ElseIf LECTURER = Worksheets("HLS").Range("LEC_1") Then
IVOLL = Worksheets("HLS").Range("INIT_1")

ElseIf LECTURER = Worksheets("HLS").Range("LEC_2") Then
IVOLL = Worksheets("HLS").Range("INIT_2")

ElseIf LECTURER = Worksheets("HLS").Range("LEC_3") Then
IVOLL = Worksheets("HLS").Range("INIT_3")

Else
IVOLL = "N"
End If

End Function

This code does not work on other Worksheet. This code only work on Worksheet named "HLS". How to make this code work on all Worksheets since the LECTURER variable call the range named LEC_1 on Worksheet named HLS.

Thanks.

 

[mikenelena]

Welcome to the board..

You'll also need to specify the name of the sheet that A1 is on that holds this worksheet name...
Assuming that is on a sheet named "Master"

Dim MySheet As String, ws As WorkSheet
MySheet = Sheets("Master").Range("A1").Value
Set ws = Sheets(MySheet)
'then refer to that worksheet like
ws.Range("A1:A10").Interior.Colorindex = 3


Hope that helps.

JonMo1,

I found this post through a search. It was helpful to me.

Thanks for posting it!!

...Mike

 

[Kubra]

Welcome to the board..

You'll also need to specify the name of the sheet that A1 is on that holds this worksheet name...
Assuming that is on a sheet named "Master"

Dim MySheet As String, ws As WorkSheet
MySheet = Sheets("Master").Range("A1").Value
Set ws = Sheets(MySheet)
'then refer to that worksheet like
ws.Range("A1:A10").Interior.Colorindex = 3


Hope that helps.

Hey thanks a lot, it helped so much