copy filtered data

G

Guest

Guest
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try this:

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Hope this helps. Cheers,

Nate
 
Upvote 0
...Or along the same lines

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet10.Range("A1")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top