VBA Code - Referencing to a Different Sheet

[jnix612]

Below is a code I use, but my "Sheet1" has another VBA code I'm using in a hidden cell. I would prefer the current code and the code you see below reference the necessary cells in a different "sheet" this way it's nice and neat and all functions like vlookup or drop down options are on another sheet.

I tested this code in a test spreadsheet. So I have "sheet1" (used below) and "sheet2" which is where B5:B7 referenced below would be moved. Can anyone help me with this part of VBA coding?

Option Explicit
Dim LastRow As Long

Sub StopTeamSort()
With Sheet1
    LastRow = .Range("E9999").End(xlUp).Row
    If LastRow < 5 Then LastRow = 5
    .Range("B6,B7").ClearContents
    .Sort.SortFields.Clear
    
    If .Range("B5").Value = "é" Or .Range("B5").Value = Empty Then
        .Range("B5").Value = "ê"
        .Sort.SortFields.Add Key:=.Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Else:
        .Range("B5").Value = "é"
        .Sort.SortFields.Add Key:=.Range("E5"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End If
    With .Sort
        .SetRange Range("E5:G" & LastRow)
        .Apply
    End With
    
End With
End Sub

[jolivanes]

Similar like following?

Dim sh2 As Worksheet
Set sh2 =Worksheets("Sheet2")
If sh2.Range("B5")= "ABCD" Then
sh2.Range("A6").Value = "Good Morning America!"

[jnix612]

@Jolivanes, are you saying this will work for me? It looks like you are creating a shortcut in the code, instead of typing Sheet2 you have it set to read Sheet2 as "sh2". I don't want to add an extra line, I just want to add the code to identify where my range of cells are located which will be in a different sheet.

[p45cal]

I don't want to add an extra line, I just want to add the code to identify where my range of cells are located which will be in a different sheet.

Change With Sheet1 to With Sheet2 or…

[jnix612]

@p45Cal..Thank you. That is simple.

[jnix612]

Hey everyone thank you for the assistance. It lead me to some other information and I got it to work. I had to leave "with sheet1" as is because I realized that's where the table is saved. I used Worksheets("Sheet2").Range("B5,B7"). This tells excel to look at sheet 2. Thank you all so much.

[jolivanes]

Isn't that what Post #2 refers to?

[jnix612]

in hindsight yes I see that now. But I'm not a coder and your message was not clear. You presented me with a question and I did not understand your message. It was not until additional research that allowed me to understand your post.

[jolivanes]

The main thing is that you've got it working.
You have to remember to use the reference to that particular sheet at the proper places.

Sheets("Sheet1").Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

more than likely will give wrong results if another sheet is the active sheet.
It needs the 2nd reference like

Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)

That's where the "sh1" comes in, a lot less typing!

[jnix612]

ahhh! OK thank you for the additional information. I really do appreciate you guys and gals.