Sub FilterAll()
Dim mainwb As Workbook
Dim flowDataSheet As Worksheet
Dim registerSheet As Worksheet
Dim lastRow As Long
Dim table1 As ListObject
Dim jobNoColumn As Range
Dim indexColumn As Range
Dim flowDataRow As Range
Dim jobNo As Variant
Dim i As Long
' Set references to the main workbook and relevant sheets
Set mainwb = ActiveWorkbook
Set flowDataSheet = mainwb.Sheets("FlowData")
Set registerSheet = mainwb.Sheets("AMSI-R-102 Job Request Register")
' Check if the worksheet has autofilter applied
registerSheet.Activate
If registerSheet.AutoFilterMode Then
registerSheet.AutoFilter.ShowAllData
End If
' Set references to the ListObject and columns in FlowData sheet
flowDataSheet.Activate
Set table1 = flowDataSheet.ListObjects("Table1")
Set indexColumn = table1.ListColumns("index").DataBodyRange
Set jobNoColumn = table1.ListColumns("Job No.:").DataBodyRange
' Loop through each row in the index column of Table1
For i = 1 To indexColumn.Rows.Count
Set flowDataRow = indexColumn.Cells(i)
' Check if the cell in the index column is empty or contains a formula that returns an empty value
If IsEmpty(flowDataRow.Value) Or flowDataRow.Value = "" Then
' Find the corresponding Job No. value in the same row of the table1 in flowdata sheet
jobNo = jobNoColumn.Cells(flowDataRow.Row - indexColumn.Row + 1).Value
' Find the last used row in column B of the register sheet
lastRow = registerSheet.Cells(registerSheet.Rows.Count, "B").End(xlUp).Row
' Copy the Job No. value to the last row + 1 in column B of the register sheet
'registerSheet.Cells(lastRow + 1, "B").Value = jobNo
registerSheet.Cells(lastRow + 1, "B").Formula = "=HYPERLINK('FlowData'!P" & i + 1 & ",'FlowData'!B" & i + 1 & ")"
End If
Next i
registerSheet.Activate
'-----Above code is to activate data flow and below is basic filtering----
Set mainwb = ActiveWorkbook
mainwb.Sheets("AMSI-R-102 Job Request Register").Activate
mainwb.Sheets("AMSI-R-102 Job Request Register").Range("A8").Select
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select
Range("B6").Value = Environ("USERNAME")
End Sub
Dim TimeToRun
Sub ScheduleclrCol()
TimeToRun = Now + TimeValue("00:00:10") '<----- adjust time to fire here HH:MM:SS
Application.OnTime TimeToRun, "tmeMacro("
End Sub
Sub FilterUserTab()
Dim mainwb As Workbook
Dim usernameSheetName As String
Dim targetSheet As Worksheet
' Below code is to update new and closed jobs
Set mainwb = ActiveWorkbook
Set flowDataSheet = mainwb.Sheets("FlowData")
Set registerSheet = mainwb.Sheets("AMSI-R-102 Job Request Register")
' Check if the worksheet has autofilter applied
registerSheet.Activate
If registerSheet.AutoFilterMode Then
registerSheet.AutoFilter.ShowAllData
End If
' Set references to the ListObject and columns in FlowData sheet
flowDataSheet.Activate
Set table1 = flowDataSheet.ListObjects("Table1")
Set indexColumn = table1.ListColumns("index").DataBodyRange
Set jobNoColumn = table1.ListColumns("Job No.:").DataBodyRange
' Loop through each row in the index column of Table1
For i = 1 To indexColumn.Rows.Count
Set flowDataRow = indexColumn.Cells(i)
' Check if the cell in the index column is empty or contains a formula that returns an empty value
If IsEmpty(flowDataRow.Value) Or flowDataRow.Value = "" Then
' Find the corresponding Job No. value in the same row of the table1 in flowdata sheet
jobNo = jobNoColumn.Cells(flowDataRow.Row - indexColumn.Row + 1).Value
' Find the last used row in column B of the register sheet
lastRow = registerSheet.Cells(registerSheet.Rows.Count, "B").End(xlUp).Row
' Copy the Job No. value to the last row + 1 in column B of the register sheet
'registerSheet.Cells(lastRow + 1, "B").Value = jobNo
registerSheet.Cells(lastRow + 1, "B").Formula = "=HYPERLINK('FlowData'!P" & i + 1 & ",'FlowData'!B" & i + 1 & ")"
End If
Next i
' Update new and closed jobe done next step is Filter user tab
ThisWorkbook.Sheets("AMSI-R-102 Job Request Register").Range("B6").Value = Environ("USERNAME")
Set mainwb = ActiveWorkbook
usernameSheetName = mainwb.Sheets("AMSI-R-102 Job Request Register").Range("B6").Value
On Error Resume Next
Set targetSheet = mainwb.Sheets(usernameSheetName)
On Error GoTo 0
If Not targetSheet Is Nothing Then
targetSheet.Activate
Else
mainwb.Sheets("AMSI-R-102 Job Request Register").Activate
Exit Sub
End If
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Range("A8:N8").AutoFilter Field:=13, Criteria1:=Range("B6")
Range("A8:N8").AutoFilter Field:=12, Criteria1:="In progress"
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A8:N" & lastRow).Sort Key1:=Range("F8:F" & lastRow), Order1:=xlAscending, Header:=xlNo
ScheduleclrCol
End Sub
Sub flowdata()
Dim mainwb As Workbook
Dim flowDataSheet As Worksheet
Dim registerSheet As Worksheet
Dim lastRow As Long
Dim table1 As ListObject
Dim jobNoColumn As Range
Dim indexColumn As Range
Dim flowDataRow As Range
Dim jobNo As Variant
Dim i As Long
' Set references to the main workbook and relevant sheets
Set mainwb = ActiveWorkbook
Set flowDataSheet = mainwb.Sheets("FlowData")
Set registerSheet = mainwb.Sheets("AMSI-R-102 Job Request Register")
' Check if the worksheet has autofilter applied
registerSheet.Activate
If registerSheet.AutoFilterMode Then
registerSheet.AutoFilter.ShowAllData
End If
' Set references to the ListObject and columns in FlowData sheet
flowDataSheet.Activate
Set table1 = flowDataSheet.ListObjects("Table1")
Set indexColumn = table1.ListColumns("index").DataBodyRange
Set jobNoColumn = table1.ListColumns("Job No.:").DataBodyRange
' Loop through each row in the index column of Table1
For i = 1 To indexColumn.Rows.Count
Set flowDataRow = indexColumn.Cells(i)
' Check if the cell in the index column is empty or contains a formula that returns an empty value
If IsEmpty(flowDataRow.Value) Or flowDataRow.Value = "" Then
' Find the corresponding Job No. value in the same row of the table1 in flowdata sheet
jobNo = jobNoColumn.Cells(flowDataRow.Row - indexColumn.Row + 1).Value
' Find the last used row in column B of the register sheet
lastRow = registerSheet.Cells(registerSheet.Rows.Count, "B").End(xlUp).Row
' Copy the Job No. value to the last row + 1 in column B of the register sheet
'registerSheet.Cells(lastRow + 1, "B").Value = jobNo
registerSheet.Cells(lastRow + 1, "B").Formula = "=HYPERLINK('FlowData'!P" & i + 1 & ",'FlowData'!B" & i + 1 & ")"
End If
Next i
registerSheet.Activate
End Sub
' The remaining code is to set a timer for document ideal time,
' Once the document is ideal for the bellow set time, the document will save and close
' Three private subs in "ThisWorkbook" are controlling the below code
'Option Explicit
'Dim CloseTime As Date
'Sub TimeSetting()
'CloseTime = Now + TimeValue("00:00:20")
'On Error Resume Next
'Application.OnTime EarliestTime:=CloseTime, _
'Procedure:="SavedAndClose", Schedule:=True
'End Sub
'Sub TimeStop()
'On Error Resume Next
'Application.OnTime EarliestTime:=CloseTime, _
'Procedure:="SavedAndClose", Schedule:=False
'End Sub
'Sub SavedAndClose()
'Application.DisplayAlerts = False
'ActiveWorkbook.Close Savechanges:=True
'Application.DisplayAlerts = True
'End Sub
Sub FilterE5()
Dim mainwb As Workbook
Dim targetSheet As Worksheet
' Set references to the main workbook and relevant sheets
'Set mainwb = ActiveWorkbook
'Set registerSheet = mainwb.Sheets("AMSI-R-102 Job Request Register")
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Range("A8:NN8").AutoFilter Field:=17, Criteria1:=Range("E5")
Range("A8:NN8").AutoFilter Field:=19, Criteria1:="In progress"
End Sub
