Posts by sluggo

sluggo · Nov 27th 2021

Attached is the Autofiltered list in ws2.

I want to copy yellow coloured cell values, to ws3(rng5), without using helper columns.

Ozgrid_211127.xlsm

sluggo · Nov 26th 2021

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 If

Display More

Any help will be much appreciated.

Thanks in advance!