Specify set of columns to be saved in each excel worksheet

Question

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

Possible duplicated question> https://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table — , Dec 04 '17 at 15:42
Another question that looks the same> https://stackoverflow.com/questions/32469561/macro-for-copying-and-pasting-visible-cells-only — , Dec 04 '17 at 15:43
@DavidG Thank you, I went through the second question you mentioned during my research but I fail to succeed integrating the CellTypeVisible. Do you suggest I declare a target range for each sheet ? — Til, Dec 04 '17 at 16:22

Johnny D · Answer 1 · 2017-12-05T06:01:12.607

I've found this to work very well. It uses the .range property of the .autofilter object, which seems to be a rather obscure, but very handy, feature:

Sub copyfiltered()
    ' Copies the visible columns
    ' and the selected rows in an autofilter
    '
    ' Assumes that the filter was previously applied
    '
    Dim wsIn As Worksheet
    Dim wsOut As Worksheet

    Set wsIn = Worksheets("Sheet1")
    Set wsOut = Worksheets("Sheet2")

    ' Hide the columns you don't want to copy
    wsIn.Range("B:B,D:D").EntireColumn.Hidden = True

    'Copy the filtered rows from wsIn and and paste in wsOut
    wsIn.AutoFilter.Range.Copy Destination:=wsOut.Range("A1")
End Sub

That looks brilliant, definitely going to test .range property. — Til, Dec 06 '17 at 10:49

Specify set of columns to be saved in each excel worksheet

1 Answers1