Copying Filtered List using Range Value

  • Hi Guys,

    Usual head banging, but hopefully someone can spot a typo to get this working. I know we can use copy and paste, but I wanted to try a different approach. Basically I want to copy a filtered list from my "Workbench" sheet to my "ClientWorkbench" sheet. Though the parameters look fine, and the sheets look filtered correctly, the below line seems to copy the first row it finds and copies that the whole way down, rather than the actual filtered range.

    Can anyone spot what I have done wrong?

    filtrw = WorksheetFunction.Subtotal(2, ThisWorkbook.Sheets("Workbench").Range(ThisWorkbook.Sheets("Workbench").Cells(2, 1), ThisWorkbook.Sheets("Workbench").Cells(WorkbenchLstRw + 1, 1)))
     ThisWorkbook.Sheets("ClientWorkbench").Range(ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRw, 1), ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRw + filtrw - 1, WorkbenchLstCl)).Value = Range(ThisWorkbook.Sheets("Workbench").Cells(2, 1), ThisWorkbook.Sheets("Workbench").Cells(WorkbenchLstRw, WorkbenchLstCl)).SpecialCells(xlCellTypeVisible).Value
  • Re: Copying Filtered List using Range Value

    Ok, im guessing you just cant write it that way as tried different ways (if the data wasn't filtered it would work of course) ... Be good if someone could explain why my original idea doesn't yield the correct results?

    I changed it to this as spent far too long playing

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!