copy filtered data

I recorded a Marco to copy filtered data, the filtered data was 26 rows but the recorder copied 724 rows, is there a way to copy just the “visible” data from the auto filter?

[NateO]

Try this:

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Hope this helps. Cheers,

Nate

[Dave Hawley]

...Or along the same lines

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet10.Range("A1")

I am using the code below to copy the auto filleted data, but the new workbook is over 2MB! What is happening the file it is coping form is only 244KB, the auto filter is only showing 12 rows
Sub move_to_new_workbook()
Application.ScreenUpdating = False
Dim CurrentFileName As String
Dim NewFileName As String
CurrentFileName = ActiveWorkbook.Name
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add Template:="Workbook"
NewFileName = ActiveWorkbook.Name
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(CurrentFileName).Activate
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub

[NateO]

Try Dave's code shown below (in this post), you're not making a selection in your code, so it's selecting the entire sheet, bringing the entire sheet, filter, format, null values, etc...And it's making it large.

Sub move_to_new_workbook()
Application.ScreenUpdating = False
Dim CurrentFileName As String
Dim NewFileName As String
CurrentFileName = ActiveWorkbook.Name
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add Template:="Workbook"
NewFileName = ActiveWorkbook.Name
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(CurrentFileName).Activate
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Hope this helps. Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-12 10:32 ]

[Dave Hawley]

Try

Sub move_to_new_workbook()

Application.ScreenUpdating = False

Sheets.Add().Name = "New Data"

Selection.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("New Data").Range("A1")

Sheets("New Data").Move

ThisWorkbook.Activate

Application.ScreenUpdating = True
End Sub

tried the one by dave only copied on cell

[NateO]

I meant the code I typed out/copied, using Dave's earlier post. Or you can try this:

Sub move_to_new_workbook()
Application.ScreenUpdating = False
Dim CurrentFileName As String
Dim NewFileName As String
CurrentFileName = ActiveWorkbook.Name
range("a1:z5000").select 'select appropriate range here
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add Template:="Workbook"
NewFileName = ActiveWorkbook.Name
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(CurrentFileName).Activate
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Hope this helps. Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-12 10:19 ]

NateO, sorry did not see the code the one you had below works. Thanks
Sub move_to_new_workbook()
Application.ScreenUpdating = False
Dim CurrentFileName As String
Dim NewFileName As String
CurrentFileName = ActiveWorkbook.Name
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add Template:="Workbook"
NewFileName = ActiveWorkbook.Name
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(CurrentFileName).Activate
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub

[NateO]

Hey no problemo Anon, all's well that ends well. Happy trails!

Cheers, Nate