Using VBA to reference a sheet in the same file that the macro is run from

[prupert]

Hello

I have a macro that I have created from lots and lots of Googling and taking ideas and snippets from various post, mostly from this very forum, so first a bigh THANKS!

I sadly know next to nothing about VBA, as I only really have BASH experience and a bit of Python, so I get lost very quickly.

My code that I have created works fine, except that the file the macro is contained within can't be renamed as my current macro references a sheet within the file. I would like the excel file that contains the macro to be called anything and the macro to still work regardless, but I can't seem to find a way to refernece the sheet within the file, I know there are various options, but none of them seem to work with the code I have.

Here is bit of code that is causing the issue:

' With Sheets("Criteria")
    With Workbooks("MyCleverMacros.xlsm").Worksheets("Library")
        Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
    End With
    'Loop through the cells in the Criteria range
    For Each cell In CriteriaRng
        WordListSheet.Activate
        With ActiveSheet
            'Firstly, remove the AutoFilter
            .AutoFilterMode = False
            'Apply the filter
            .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value
            With .AutoFilter.Range
                Set rng = Nothing
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then rng.EntireRow.Delete
            End With
            'Remove the AutoFilter
            .AutoFilterMode = False
        End With
    Next cell

Basically, what this part of the code does is it reads through the words listed in the sheet called "Library" and deletes them from the selected cells if they occur. The problem is this line: With Workbooks("MyCleverMacros.xlsm").Worksheets("Library") as you can see it references a specific workbook called MyCleverMacro and a sheet within it called Library. I want it to reference the sheet called Library only and not care about the actual name of the XLSM file. The macro that runs this bit of code will always be contained within the XLSM file that contains the Library sheet, so that isn't the problem, I just can't work out how to refence the actual Sheet only based on the code I am using. I know it is something along the lines of Sheets("Library").Select or simply Worksheets("Library"), but that doesn't seem to work with the way my code is written.


Can anyone help?

 

[VoG]

Try

With ThisWorkbook.Worksheets("Library")

 

[Phanmore]

Double post sorry

 

[prupert]

Try

With ThisWorkbook.Worksheets("Library")

Awesome, worked like a dream, I knew it was something simple, thanks so much!