Seach All Worksheets for Value >= User Input

Hello All,

Im trying to develop an excel workbook that I can use as a template for PDF's I convert to .xls files in order to search for data and manipulate it.

I've tried messing with VB and I cant seem to get it right. Can anyone help me figure out how to accept user input for a value as well as accept a column letter and then have a macro seach all worksheets in the workbook for any value >= to the input...Then copy and paste it to the first sheet?

IF anyone has any code readily available, or help..I would greatly appreciate it.

I can currently search 1 sheet and cut and paste the exact value of the input but thats it. The code is below.

Thanks


Option Explicit

Const rtDataSheet As String = "Sheet1" 'Change to actual data sheet tab name
Const rtResultSheet As String = "Sheet8" 'Change to actual result sheet tab name

Dim ColName As String
Dim SearchStr

'Main subroutine...
Sub FindAndCopy()
    SearchStr = InputBox("Enter ISI")
    
    If SearchStr = "" Then Exit Sub
    
    ColName = GetColName(InputBox("Which Column contains the dollar amounts?  Name or Number"))
    
    If ColName = "" Then Exit Sub
    
    Dim SearchRange As Range
    Set SearchRange = Worksheets(rtDataSheet).Range(ColName & "2:" & ColName & "65536")  '2 - exclude 1st row
   

    
    
    
    
    
    'The following codes are a modified version of the 'Find' method example from the VBA Help
    Dim FoundVal As Range
    Dim FirstAddress As String
    
    
    With SearchRange
        Set FoundVal = .Find(SearchStr, MatchCase:=False)
        
         
         If Not FoundVal Is Nothing Then
            FirstAddress = FoundVal.Address
            Do
                
                CopyToResultSheet FoundVal.EntireRow
                Set FoundVal = .FindNext(FoundVal)
                
            Loop While (Not FoundVal Is Nothing) And (FoundVal.Address <> FirstAddress)
        End If
    End With
End Sub

'Returns the column name for the specified column number.
Function GetColName(ByVal ColNum As String) As String
    'Excel (as of this version) only holds up to 256 columns (from A - IV)
    Dim ColName As String
    ColName = "<OVERFLOW>"
    
    If IsNumeric(ColNum) Then
        If (ColNum >= 1) And (ColNum <= 256) Then
            ColName = ""
        
            If ColNum > 26 Then
                ColName = Chr((Asc("A") - 1) + Int((ColNum - 1) / 26))
            End If
            
            ColName = ColName & Chr(Asc("A") + ((ColNum - 1) Mod 26))
        End If
    Else
        ColName = ColNum
    End If
    
    GetColName = ColName
End Function

'Copies the found values to result sheet
'It is assumed that columns G and H are empty in data sheet
Sub CopyToResultSheet(ByVal FoundVal As Range)
    Dim LastRow As Long
    LastRow = GetRSLastRow

    FoundVal.Copy Worksheets(rtResultSheet).Range("A" & LastRow + 1)
    Worksheets(rtResultSheet).Range("K" & LastRow + 1).Value = "Search String: " & SearchStr
    Worksheets(rtResultSheet).Range("L" & LastRow + 1).Value = "From " & rtDataSheet & " Cell " & ColName & FoundVal.Row
End Sub

'Gets the last occupied row in result sheet
Function GetRSLastRow() As Long
    Dim RowRange As Range
    Set RowRange = Worksheets(rtResultSheet).Range("A65536").End(xlUp)
    
    GetRSLastRow = RowRange.Row
End Function