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