VBA Code - Referencing to a Different Sheet

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?

Code:

---

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

---

Similar like following?

Code:

---

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

---

@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.

Quote:

---

Originally Posted by jnix612

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…

@p45Cal..Thank you. That is simple. :friends:

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. :hi:

Isn't that what Post #2 refers to?

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.

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.

Code:

---

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

Code:

---

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!

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