Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Copy filtered rows to another sheet skip last row

    Hello,

    I'm trying to copy some filtered rows (including the criterie "blank") from several sheets to one sheet only but when the last row have the criteria BLANK the row is not copied.

    The code that I'm using is:
    -------------------------------------------------------------------------------------
    'Cycle through worksheets
    With Worksheets("MKT")
    For i = 1 To Worksheets.Count
    Worksheets(i).Unprotect Password:="1"
    If Worksheets(i).Name <> "MKT" Then
    Worksheets(i).Activate

    'Filter Sheets and copy rows to MKT
    Worksheets(i).Range("B7:L7").AutoFilter Field:=9, Criteria1:=Array( _
    "Opt1", "Opt2", "="), Operator:=xlFilterValues
    LastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
    For Each targetrow In Worksheets(i).Range("B8:L" & LastRow).Rows
    If targetrow.Hidden = False Then
    For J = 2 To 11
    .Cells(sourcerow, J - 1) = Cells(targetrow.Row, J)
    Next J
    sourcerow = sourcerow + 1
    End If
    Next targetrow

    'Protect Sheets
    Worksheets(i).Protect Password:="1", AllowFiltering:=True
    End If
    Next i
    End With

    -------------------------------------------------------------------------------------

    What I'm doing wrong?

    Many thanks for any help


    LL

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Confusing. Provide file with examples

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    LL:

    Try using CurrentRegion combined with selecting visible cells only and then paste.
    Something like the below

    'Filter Sheets and copy rows to MKT
    Worksheets(i).Range("B7:L7").AutoFilter Field:=9, Criteria1:=Array( _
    "Opt1", "Opt2", "="), Operator:=xlFilterValues
    'Worksheets(i).Range("B8").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Seleciton.Copy
    Put code here to make active workbook MKT and place where data will be pasted
    ActiveSheet.Paste

    LastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
    For Each targetrow In Worksheets(i).Range("B8:L" & LastRow).Rows
    If targetrow.Hidden = False Then
    For J = 2 To 11
    .Cells(sourcerow, J - 1) = Cells(targetrow.Row, J)
    Next J
    sourcerow = sourcerow + 1
    End If
    Next targetrow

    Hope this helps

    TD

  4. #4
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thanks for the help duthiet but even so the problem remains...

    Let's see if I can explain better with an example file.

    Please find attached a file with 3 users (sheets) (that can be N( and 1 more sheet "MKT".

    The idea is to copy all filtered rows from each sheet to MKT sheet.

    If you check on Sheet "User1" we have 4 rows to be copied but it only copies 2 to MKT sheet. In "UserN" sheet, the same, i.e. miss last row.

    From "User2" copies all the rows because the last row have data in Feedback column.

    Do you have an idea what can be wrong?

    Many thanks in advance


    LL
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hello

    Please see post below the attached file and description.

    Many thanks for any help


    LL

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Lucial,

    I believe this is a very simple fix. Your problem lies in the code line:

    LastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row

    You are referencing column 10 as the index to count rows. The code works by starting at the bottom and goes up the column until it meets the first value and calls that the last row. When the last 2 filtered values in column 10 are blank, the variable LastRow is assigned the wrong number, In your sample, sheet User1, LastRow would be 2 instead of 6.

    Change the 10 to a 1, 5, or 7 depending on which of those columns would always have a value.

    HTH,
    Maud

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    LuciaLourenco (2014-09-26)

  8. #7
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Many, many thanks Maudibe

    Problem solved

    LL

  9. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You could also use specialcells to find the last cell and use that row. Column doesn't matter.

    lr = Cells.SpecialCells(xlCellTypeLastCell).Row

Posting Permissions

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