Copy/Filter/Paste to TXT File- Remove Excel Filter

Question

I am trying to filter a tab in excel for non blank cells, copy those cells, and paste the copied data in a txt file. I then need the filter applied on the tab in excel to be removed. When I try to input the clear sub in the TEST sub, the copy and paste to the TXT file is blank. I would like to only have one macro instead of a separate one to remove the excel filter. Thank you!

Sub TEST()
    Dim copysheet As Worksheet
    Dim TextFile As Integer
    
    Set copysheet = ActiveWorkbook.Sheets("temptotext")
    

    Sheets("temptotext").Select
    Rows("1:1").Select
    Selection.AutoFilter
    copysheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:="<>"
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    
    Shell "notepad.exe", vbNormalFocus
    SendKeys "^v"
End Sub

Sub clear()
Dim copysheet As Worksheet
    
    Set copysheet = Sheets("temptotext")
    copysheet.Select
    Cells.AutoFilter
    
End Sub

Since all you are doing is creating a Text file, `SendKeys` is the wrong tool. Use [`Print'](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/printstatement) or ['FileSystemObject'](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object) to create the file. — chris neilsen, Feb 17 '22 at 22:56

VBasic2008 · Answer 1 · 2022-02-20T04:54:22.307

Copy Filtered Data to Notepad

Option Explicit

Sub CopyFilteredData()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("TempoText")
    If ws.FilterMode Then ws.ShowAllData ' remove previous filter
        
    Dim rg As Range ' (Table) Range (has headers)
    Set rg = ws.Range("A1").CurrentRegion
    Dim drg As Range ' Data Range (no headers)
    Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    
    rg.AutoFilter Field:=2, Criteria1:="10"  ' filter the table range
    
    Dim vdrg As Range ' Visible Data Range (will be 'Nothing' if only headers)
    On Error Resume Next
        Set vdrg = drg.SpecialCells(xlCellTypeVisible) ' use data range
    On Error GoTo 0
    
    If vdrg Is Nothing Then Exit Sub ' no filtered data
    
    vdrg.Copy
        
    Shell "notepad.exe", vbNormalFocus
    SendKeys "^v"
     
    DoEvents
    
    ' Either
    ws.ShowAllData ' to stay in 'AutoFilterMode' (keep the arrows)
    ' or
    'ws.AutoFilterMode = False ' to exit 'AutoFilterMode' (no arrows)
     
    Application.CutCopyMode = False
    'wb.Close SaveChanges:=True

End Sub

Copy/Filter/Paste to TXT File- Remove Excel Filter

1 Answers1

Copy Filtered Data to Notepad