PDA

View Full Version : VBA Code - Referencing to a Different Sheet



jnix612
09-06-2018, 11:54 AM
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
09-06-2018, 05:24 PM
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
09-07-2018, 05:26 AM
@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
09-07-2018, 06:40 AM
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
09-07-2018, 06:47 AM
@p45Cal..Thank you. That is simple. :friends:

jnix612
09-07-2018, 07:40 AM
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:

jolivanes
09-07-2018, 09:30 AM
Isn't that what Post #2 refers to?

jnix612
09-07-2018, 09:39 AM
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
09-07-2018, 09:52 AM
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
09-07-2018, 11:50 AM
ahhh! OK thank you for the additional information. I really do appreciate you guys and gals. :thumb