VBA reference cell value to be worksheet name

treehook

New Member
Joined
Mar 28, 2013
Messages
2
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 
Upvote 0

skyline7441

New Member
Joined
Dec 24, 2015
Messages
1
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.
 
Upvote 0

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Kubra

New Member
Joined
Feb 6, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 
Upvote 0

Forum statistics

Threads
1,195,712
Messages
6,011,259
Members
441,598
Latest member
chrispaulpearce

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top