Export (VBA) an Excel 2003 List (Filtered) to a txt/csv/xls

[danielparra]

Hello Everybody,

I'm hoping somebody has the solution for this I already browsed through
most of the internet without any luck.

ok so let's say I have an excel spreadsheet:
A1: label1 B1: label2
A2: value1 B2: 1
A3: value2 B3: 1
A4: value3 B4: 3
A5: value4 B5: 1
A6: value5 B6: 3
A7: value6 B7: 1
A8: value7 B8: 3

I select (A1:B8) and click on create list
now I click on the drop down button of label2 and select 3 this shows
only the rows for which label2 value is 3 (basic filtering)

it would show:

A1: label1 B1: label2
A4: value3 B4: 3
A6: value5 B6: 3
A8: value7 B8: 3

OK. now I need to be able to export the filtered sheet either to a text
file, csv file, an excel file, another sheet, with a button.
the usual methods of exporting won't work because they use a for to go
through each row and this would export also the hidden rows.

going into edit->GoTo->Special->Visible Cells Only and then manually
selecting the cells and copying and pasting into another sheet is not
an answer since the spreadsheet will dynamically grow that's why I need
a VBA code that exports the current view of the sheet.

I would really appreciate any suggestions

 

[Dave Peterson]

You could have a macro do the work for you.

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = ActiveSheet
Set NewWks = Workbooks.Add(1).Worksheets(1)

CurWks.AutoFilter.Range.Copy _
Destination:=NewWks.Range("a1")

With NewWks.Parent
.SaveAs Filename:="C:\myfile.csv", FileFormat:=xlCSV
.Close savechanges:=False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Lava]

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Stumbled on this thread by chance. I'm not completely new to macros,
but there's much for me to learn from that website. Thanks a lot.

 

[danielparra]

Thanks Dave I really appreciate your help.

I wanted to give the user a little bit more control so this is how it
ended thanks to your suggestions and the ones from BrianB from
Luton,England from MrExcel Message Boards.
http://www.mrexcel.com/board2/viewtopic.php?p=848773#848773

Private Sub CommandButton1_Click()
On Error Resume Next
Application.DisplayAlerts = False
Sheets("filtered").Delete
Application.DisplayAlerts = True
Dim wb As Workbook ' this
Dim ws As Worksheet ' this
Dim MyRange As Range
Dim LastRow As Long
Dim FileName As String ' for text file
Dim MyRow As Long
Dim MyCol As Integer
Dim ColumnCount As Integer ' number of columns
'---------------------------------------------------------------
'- initialize variables
Set wb = ThisWorkbook
Set ws = ActiveSheet
LastRow = ws.Range("A65536").End(xlUp).Row
ColumnCount = 2
'----------------------------------------------------------------
'- Copy visible cells
Set MyRange = ws.Range(Cells(1, "A"), _
Cells(LastRow,
ColumnCount)).SpecialCells(xlCellTypeVisible)
MyRange.Copy

'===================================================================
'- paste to workbook (containing macro).Could use 'ActiveWorkBook'
?
Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = "filtered"
wb.Worksheets("filtered").Paste _
Destination:=wb.Worksheets("filtered").Range("A1")

'===================================================================
'- to a comma delimited text file
FileName = Application.GetSaveAsFilename("C:\", "(*.csv), *.csv", ,
"")
If FileName <> False Then
Open FileName For Output As #1
For MyRow = 1 To LastRow ' - 1
If ws.Rows(MyRow).EntireRow.Hidden = False Then
For MyCol = 1 To ColumnCount
If ((MyRange.Cells(MyRow, MyCol).Value = "") And
(MyRange.Cells(MyRow, MyCol + 1).Value = "")) Then
MyCol = ColumnCount
Else
Print #1, MyRange.Cells(MyRow, MyCol).Value;
If MyCol < ColumnCount Then Print #1, ",";
End If
Next
Print #1, ' end of line
End If
Next

'-------------------------------------------------------------------
Close #1

'===================================================================
MsgBox ("Done")
End If
End Sub