How to Copy last 20 visible cells in auto filtered range? I'm not a fan of helper columns (see macro).
ws2 and ws3 are in different workbooks.
"Nor" is a cell referens to a third workbook but 20 in this example.
Anyone knows how to do that?
Code
Nor = 20
lr = ws2.Cells(Rows.count, "A").End(xlUp).Row
Set rng3 = ws2.range("E2:F" & lr)
Set rng5 = ws3.range("A2") 'Col.A-B
Set rng6 = ws3.range("C2") 'Helper Col.C-D
If ws2.AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then
If ws2.AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count > 1 And _
ws2.AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count <= Nor Then
rng3.SpecialCells(xlCellTypeVisible).Copy
rng5.PasteSpecial xlPasteValues
ElseIf ws2.AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count > Nor Then
'Want to do something like this:
ws2.range(ws2.Cells(lr, 5).Offset(-Nor), ws2.Cells(lr, 6)).SpecialCells(xlCellTypeVisible).Copy
rng5.PasteSpecial xlPasteValues
'but have to use helper columns like this:
rng3.SpecialCells(xlCellTypeVisible).Copy
rng6.PasteSpecial xlPasteValues
lr2 = ws3.Cells(Rows.count, "C").End(xlUp).Row 'C-D = helper columns
ws3.range(ws3.Cells(lr2, 3).Offset(-Nor), ws3.Cells(lr2, 4)).Copy
rng5.PasteSpecial xlPasteValues
End If
End IfAny help will be much appreciated.
Thanks in advance!