I am working with a workbook containing multiple sheets and I have to export them on a regular basis. My sheets have thousands of rows, using an autofilter I would like to export only the filtered content and visible columns.
What I am missing is a way to export only the visible columns and rows. I think I need to use CellTypeVisible but have not found how to make it work in my code:
Sub SaveAllAsTsv()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim Filename As String
'Get folder to save to
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Output Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
SaveToDirectory = .SelectedItems(1)
End With
'Choose which worksheets to save
For Each WS In ActiveWorkbook.Worksheets
Select Case MsgBox("Save " & WS.Name & "?", vbQuestion + vbYesNoCancel)
Case vbYes
Filename = SaveToDirectory & "\" & WS.Name & ".txt"
WS.SaveAs Filename, xlTextWindows, Local:=True
Case vbCancel
Exit Sub
Case vbNo
End Select
Next
End Sub