Copying Filtered List using Range Value

M40wen · May 11th 2016

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?

Code

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

M40wen · May 11th 2016

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

Code

Range(ThisWorkbook.Sheets("Workbench").Cells(2, 1), ThisWorkbook.Sheets("Workbench").Cells(WorkbenchLstRw + 1, WorkbenchLstCl)).SpecialCells(xlCellTypeVisible).Copy
                ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRw, 1).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
                ClientWorkbenchRw = ClientWorkbenchRw + filtrw - 1
'check for blanks
                    If ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRw, 1) = "" Then
                       Do Until ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRw, 1) <> "" Or ClientWorkbenchRw < 2
                       ClientWorkbenchRw = ClientWorkbenchRw - 1
                       Loop
                    End If
                    ClientWorkbenchRw = ClientWorkbenchRw + 1

Display More

Copying Filtered List using Range Value

Participate now!

Share