Thread: Finding Range of Values Across Multiple Sheets

harpseal,

You can accomplish what you're requesting with a macro. To add a macro to a workbook:

1. Save a copy of the Excel workbook you want to modify
   • Always test macros in a copy so that the original is preserved in case the modifications don't go smoothly
2. Open the copy of the Excel workbook you want to modify
3. Use the keyboard shortcut ALT+F11 to open the Visual Basic Editor
4. Insert -> Module
5. Copy/Paste the code into that area

To run a macro in a workbook:

1. In Excel (not the Visual Basic Editor) press the keyboard shortcut ALT+F8
2. Double-click the desired macro (I named this one ExportDataMacro_for_harpseal)

Here's the macro code. It will save "valid" rows of a worksheet to your desktop as a .txt file:

Sub ExportDataMacro_for_harpseal()
    
    Const Col1 As String = "A"   'The first column to check
    Const Col2 As String = "B"   'The second column to check
    Const LowVal As Double = 1   'The low value you're looking for
    Const HiVal As Double = 10   'The high value you're looking for
    
    Dim SavePath As String: SavePath = Environ("UserProfile") & "\Desktop\"
    Dim FileName As String
    
    Dim wsDest As Worksheet: Set wsDest = Sheets.Add(after:=Sheets(Sheets.Count))
    wsDest.Name = "Data dump for valid data"
    
    Dim ws As Worksheet, RowIndex As Long, rngValid As Range
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsDest.Name Then
            wsDest.UsedRange.ClearContents
            ws.UsedRange.AutoFilter
            ws.UsedRange.AutoFilter Field:=ws.Cells(1, Col1).Column, _
                                    Criteria1:=">=" & LowVal, Operator:=xlAnd, Criteria2:="<=" & HiVal
            ws.UsedRange.AutoFilter Field:=ws.Cells(1, Col2).Column, _
                                    Criteria1:=">=" & LowVal, Operator:=xlAnd, Criteria2:="<=" & HiVal
            Set rngValid = ws.UsedRange.SpecialCells(xlCellTypeVisible)
            rngValid.Copy
            wsDest.[A1].PasteSpecial xlPasteAll
            ActiveSheet.SaveAs FileName:=SavePath & ws.Name & ".txt", _
                               FileFormat:=xlUnicodeText
            ws.UsedRange.AutoFilter
        End If
    Next ws
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    wsDest.Delete
    Application.DisplayAlerts = True
    
End Sub

Hope that helps,
~tigeravatar