How to not require a sheet to be 'selected'

[Benlav]

Hi team,

I've gotten into the habit of creating code like so, which requires the .select behaviour. Which is all well and good but I now want to not have to make tabs visible due to the limitations of my code. How would you suggest I get the same output, without the use of .select which requires a visible and active tab?

I know my code aint pretty but it works... Could be a lot better though.

To add context, I have a share point list which has a name, i.e. "review document", then the next column is the location of that word document template. I have hidden sheets which pull down the lists, and when the user wants to create their document, it looks for the type of document they're trying to create, looks up the cell on the hidden sheet, moves over one column and that is the file location.

Thanks,

Ben

Dim FileLocation As String

On Error Resume Next
ThisWorkbook.Sheets("File Paths").Visible = True
ThisWorkbook.Sheets("File Paths").Select

lastrow = ThisWorkbook.Sheets("File Paths").range("A" & Rows.Count).End(xlUp).row

Set TemplateRange = ThisWorkbook.Sheets("File Paths").range("B2:B" & lastrow)
For Each C In TemplateRange
    If C.Value = "Review Document" Then
        C.Select
        ActiveCell.Offset(0, 1).Select
        FileLocation = ActiveCell.Value
        Exit For
    End If
Next C


ThisWorkbook.Sheets("File Paths").Visible = False

edit: poor grammar

 

[Jaafar Tribak]

Untested :

Dim FileLocation As String, lastrow As Long
Dim TemplateRange As Range, c As Range, Offset As Range

On Error Resume Next

With ThisWorkbook.Sheets("File Paths")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set TemplateRange = .Range("B2:B" & lastrow)
    For Each c In TemplateRange
        If c.Value = "Review Document" Then
            Set Offset = c.Offset(0, 1)
            FileLocation = Offset.Value
            Exit For
        End If
    Next c
End With

 

[Michael M]

Maybe this

Sub MM1()
Dim FileLocation As String
On Error Resume Next
Set ws = Sheets("File Paths")
ws.Visible = True
Set TemplateRange = ws.Range("B2:B" & ws.Range("A" & Rows.Count).End(xlUp).Row)
For Each c In TemplateRange
    If c.Value = "Review Document" Then
        FileLocation = c.Offset(0, 1).Value
        Exit For
    End If
Next c
ws.Visible = False
End Sub

 

[Benlav]

thanks team, i'll have a play tonight and see.

 

[Norie]

If you only want to find 'Review Document' in column B on the sheet 'File Paths' are you sure you need to loop?

Dim FileLocation As String
Dim Res As Variant

    With ThisWorkbook.Sheets("File Paths")
        Res  = Application.Match("Review Document", .Columns(2), 0)
        If Not IsError(Res) Then
            FileLocation = .Cells(Res,3).Value
        End If
    End With

 

[Benlav]

If you only want to find 'Review Document' in column B on the sheet 'File Paths' are you sure you need to loop?

Dim FileLocation As String
Dim Res As Variant

    With ThisWorkbook.Sheets("File Paths")
        Res  = Application.Match("Review Document", .Columns(2), 0)
        If Not IsError(Res) Then
            FileLocation = .Cells(Res,3).Value
        End If
    End With

Thank you. That looks tidier. In short, no, but I didn't know how else to do it. I definitely like that much better. Thank you. Will load it up and report back.