Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy range based on flag (Excel 2003)

    hello all,
    I know, it is possible to copy a range - marked via flag by the use of autofilter. How can it be done
    via vba? - the range, marked via flag - "x" on table data should be copied into the first row of table
    Summary.
    stef
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy range based on flag (Excel 2003)

    You could use AutoFilter:
    <code>
    Sub CopyFiltered()
    Dim m As Long
    Worksheets("Summary").Cells.ClearContents
    m = Worksheets("Data").Range("A65536").End(xlUp).Row
    Worksheets("Data").Range("A1:F" & m).AutoFilter _
    Field:=6, Criteria1:="x"
    Worksheets("Data").Range("A1:C" & m).Copy _
    Destination:=Worksheets("Summary").Range("A1")
    Worksheets("Data").Range("A1").AutoFilter
    End Sub</code>

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range based on flag (Excel 2003)

    thanks again for Your quick help.
    - the only thing i have to change now is, that it only works "one" time, so i have to offset something in the code to get the copy as often as i want into the first
    emtpty row.
    stef

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy range based on flag (Excel 2003)

    You stated in your original post that you wanted to copy to the first row, not to the first empty row, so that's what the code I posted does.
    Try this version:
    <code>
    Sub CopyFiltered()
    Dim m As Long
    Dim n As Long
    m = Worksheets("Data").Range("A65536").End(xlUp).Row
    n = Worksheets("Summary").Range("A65536").End(xlUp).Ro w + 1
    Worksheets("Data").Range("A1:F" & m).AutoFilter _
    Field:=6, Criteria1:="x"
    Worksheets("Data").Range("A2:C" & m).Copy _
    Destination:=Worksheets("Summary").Range("A" & n)
    Worksheets("Data").Range("A1").AutoFilter
    End Sub</code>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy range based on flag (Excel 2003)

    sorry Hans, my writing was to quick ....
    stef

Posting Permissions

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