Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: copy filtered data

  1. #1
    Guest

    Default

    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?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    Hope this helps. Cheers,

    Nate

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...Or along the same lines

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


  4. #4
    Guest

    Default

    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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Guest

    Default

    tried the one by dave only copied on cell

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    Guest

    Default

    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

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Cheers, Nate

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •