Copy filtered rows to another sheet skip last row

Topic

New Reply

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

Reply | Quote

Replies

Confusing. Provide file with examples

Reply | Quote

Hello

Please see post below the attached file and description.

Many thanks for any help

LL

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Many, many thanks Maudibe

Problem solved

LL

Reply | Quote

You could also use specialcells to find the last cell and use that row. Column doesn’t matter.

lr = Cells.SpecialCells(xlCellTypeLastCell).Row

Reply | Quote