VBA Pass string variable to refer to worksheet

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
62
Hi All.

I am trying to refer to a worksheet by a string which is value of a cell.

This is my code for this part:

Code:
    month = Sheet15.Range("K1").Value                           'This is the string with the name of the worksheet (User selectable)
    Set y = Worksheets("month")                                   'Here I want to set which sheet we will run the code on
    brlastrow = y.Range("A" & Rows.Count).End(xlUp).Row         'here I want to find the lastrow on that sheet
    Set bigrange = y.Range("C2:C" & brlastrow)                  'Here I would set the range for my loop
On the Set y row it craps out with subscript out of range error.

How can I set y to the name of the worksheet that is month?
I was going with the same syntax we use for referring to named ranges. Am I missing something?

Here are my Variables

Dim month As String
Dim y As Worksheet
Dim brlastrow As Integer
Dim bigrange As Range
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,772
Office Version
365
Platform
Windows
Remove the " from month, otherwise the macro thinks you are looking for a sheet called "month"
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
62
Thanks Fluff!

This helped.
Admittedly it was a rookie mistake
"Need a brew....." :LOL:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,772
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,085,652
Messages
5,384,950
Members
401,927
Latest member
commae

Some videos you may like

This Week's Hot Topics

Top