I have an input form worksheet (inputWks "Input") that populates several data fields for a specific record using a macro. The data for all records is stored on a separate worksheet (historyWks "Data") in the same workbook. Each record is on its own row on the Data worksheet. There are 4 buttons on the input form (View First Record, View Next Record, View Previous Record, View Last Record). I've included the code for the View Next Record below. All of the macros are working fine, but I would like to add a filter to only show records with a certain value in Column P of the Data worksheet.

Column P on the Data worksheet is either "ACTIVE" or "INACTIVE" for each record. If it is "INACTIVE" I want the View Next Record to skip over it and go to the next record that is "ACTIVE". I will also need to apply this to the macros that run the other scrolling options. For example, if the first record is "INACTIVE" then I would want the View First Record macro to go to the first record that is "ACTIVE".

I'm new to VBA and have tried many different things with no success. Any help is much appreciated!


Sub ViewLogDown()

Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range

Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim LastRow As Long
Application.EnableEvents = False

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Data")
Set rngA = ActiveCell

With historyWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = LastRow - 1
End With

With inputWks
lRec = .Range("CurrRec").Value
If lRec < lLastRec Then
.Range("CurrRec").Value = lRec + 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1

historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 7)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Transpose:=True
historyWks.Range(historyWks.Cells(lRecRow, 8), historyWks.Cells(lRecRow, 11)).Copy
.Range("F5").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Transpose:=True
historyWks.Range(historyWks.Cells(lRecRow, 12), historyWks.Cells(lRecRow, 16)).Copy
.Range("H5").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Transpose:=True
historyWks.Range(historyWks.Cells(lRecRow, 17), historyWks.Cells(lRecRow, 20)).Copy
.Range("J5").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Transpose:=True

Dim r01 As Range, r02 As Range, r03 As Range

Set r01 = historyWks.Range(historyWks.Cells(lRecRow, 21), historyWks.Cells(lRecRow, 21))
Set r02 = inputWks.Range("D10")
Set r03 = historyWks. _
Range(historyWks.Cells(lRecRow, 21), historyWks.Cells(lRecRow, 21)).MergeArea

r03.Copy
r02.PasteSpecial xlPasteAllUsingSourceTheme

inputWks.Range("OrderSel").Value = .Range("D5").MergeArea.Cells(1, 1).Value
rngA.Select
End If
End With
Application.EnableEvents = True

End Sub