CODEWITHSUNDEEP
excelmacrosvba
Lee Rosario
Lee Rosario

Reputation: 1

VBA Excel - Defining a workbook and worksheet variable value from cell

I have searched for this all day but have not been able to work it out, so hopefully y'all can help :)

Objective: For a specified sheet in a separate workbook (from which this code is ran), for a list of row numbers in column O, the script should run and highlight all of the corresponding row numbers yellow.

Context: I'm an auditor so we often have to pick random samples from Excel workbooks, with 1000s of rows and then highlight a random selection manually. I'd like to automate this procedure.

Specific problem: Have my loop working and highlighting yellow, but cannot seem to get the variables for the 'target location' for which I want the code to run to work. When I hard-code "Sheet2", then it works on Sheet 2. But even if I try just using a cell value as a variable, and in that cell putting "Sheet2", it just won't work. I've tried many more ways than what I've included below, but I just tidied up the current code and pasted below.

Details for end deliverable (all following inputs are in a single-sheet WB, sheet name is called 'Random': - User enters a folder location in cell A5 - User enters a folder location in cell A6 - User enters a sheet name in cell A7 - User pastes list of numbers for highlighting into column O - The user should then hit 'Highlight samples' button for which the code should then highlight all the row numbers in column O yellow for the target worksheet in target workbook.

Code (modified from another post):

Sub sample()
    Dim i As Long, workbk As Workbook, worksh As Worksheet  

    On Error GoTo Whoa

    Application.ScreenUpdating = False


    Set worksh = Sheets("Sheet2") 'Hard-coded, this works

    'Set worksh = Range("B6").Value 'Doesn't work
    'Set worksh = Sheets(Range("pickupsheetname")) 'Doesn't work


    With Sheets("RandomList")
        For i = 1 To 30
            If Not Len(Trim(.Range("O" & i).Value)) = 0 And _
            IsNumeric(.Range("O" & i).Value) Then _
                 worksh.Rows(.Range("O" & i).Value).Interior.ColorIndex = 6 '<~~ Yellow
        Next i
    End With

LetsContinue:
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Upvotes: 0

Views: 1747

Answers (1)

Tianna Wrona
Tianna Wrona

Reputation: 342

Try this, it worked for me (as long as the value in the cell is the name of the sheet):

Set worksh = Sheets(Range("B6").Value)

The reason why 

 Set worksh = Range("B6").Value

did not work is because now worksh is attempting to be set as a variant. You still need to define it as being a sheet by putting that word and that pair of parenthesis you see in my solution. :)

Upvotes: 1

Related Questions