Hi All,
This is my current code to activate sheet based on the value in B6 than perform filtering and sorting.
Private Sub Workbook_Open()
Dim mainwb As Workbook
Set mainwb = ActiveWorkbook
mainwb.Sheets("AMSI-R-102 Job Request Register").Activate
mainwb.Sheets(Range("B6").Value).Activate
mainwb.Sheets(Range("B6").Value).Range("A8").Select
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Range("A6:I6").AutoFilter field:=8, Criteria1:=Range("B6")
Range("A6:I6").AutoFilter field:=7, Criteria1:="In progress"
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A8:I" & lastrow).Sort key1:=Range("D8:D" & lastrow), _
order1:=xlAscending, Header:=xlNo
End Sub
The cell B6 picks up username and opens tab with matching tab name than performs remaining code.
Currently if the username opening document does not have existing tab code spits out error message, how do I make code to check if “mainwb.Sheets(Range("B6").Value).Activate” is true otherwise stop macro and keep mainwb.Sheets("AMSI-R-102 Job Request Register").Activate?
This is my current code to activate sheet based on the value in B6 than perform filtering and sorting.
Private Sub Workbook_Open()
Dim mainwb As Workbook
Set mainwb = ActiveWorkbook
mainwb.Sheets("AMSI-R-102 Job Request Register").Activate
mainwb.Sheets(Range("B6").Value).Activate
mainwb.Sheets(Range("B6").Value).Range("A8").Select
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Range("A6:I6").AutoFilter field:=8, Criteria1:=Range("B6")
Range("A6:I6").AutoFilter field:=7, Criteria1:="In progress"
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A8:I" & lastrow).Sort key1:=Range("D8:D" & lastrow), _
order1:=xlAscending, Header:=xlNo
End Sub
The cell B6 picks up username and opens tab with matching tab name than performs remaining code.
Currently if the username opening document does not have existing tab code spits out error message, how do I make code to check if “mainwb.Sheets(Range("B6").Value).Activate” is true otherwise stop macro and keep mainwb.Sheets("AMSI-R-102 Job Request Register").Activate?