Copy data from one work sheet usiing criteria to another worksheet without changing original worksheet

Question

I've been working on a VBA macro to copy data that matches certain criteria from one worksheet to another worksheet without altering the original worksheet.

I'm locating the last row from worksheet "Prospects" and selecting the criteria that I need and it copies over to the other worksheet "Results", but both worksheets look identical.

So any rows that don't meet the filter criteria are removed from the original worksheet "Prospects".

I need the original worksheet to remain unaltered. I'm also just capturing certain columns, thus hiding the columns that I don't need on the "Results" worksheet.

Sub ProspectList()

    Dim r As Range

    Dim ws As Worksheet

    Set ws = ActiveSheet

    ws.Range("A1").AutoFilter


    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlUp).Column

    With Sheets("Prospect List").Range([A2], [A2].SpecialCells(xlCellTypeLastCell))
        ws.Range("A1").AutoFilter field:=13, Criteria1:="Pipeline"
        [B:B].EntireColumn.Hidden = True
        .Copy
        [C:C].EntireColumn.Hidden = True
        .Copy
        [E:E].EntireColumn.Hidden = True
        .Copy
        [H:H].EntireColumn.Hidden = True
        .Copy
        [I:I].EntireColumn.Hidden = True
        .Copy
        [K:K].EntireColumn.Hidden = True
        .Copy
        [L:L].EntireColumn.Hidden = True
        .Copy
        [B:B].EntireColumn.Hidden = False
        [C:C].EntireColumn.Hidden = False
        [E:E].EntireColumn.Hidden = False
        [H:H].EntireColumn.Hidden = False
        [I:I].EntireColumn.Hidden = False
        [K:K].EntireColumn.Hidden = False
        [L:L].EntireColumn.Hidden = False
    End With

    With Sheets("Results")
        If .Cells(Sheets(1).Rows.Count, 1).End(xlUp) = "" Then 'it's a clean sheet
            .Cells(Sheets(1).Rows.Count, 1).End(xlUp).PasteSpecial Paste:=xlPasteValues
        Else
            .Cells(Sheets(1).Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    End With
    Application.CutCopyMode = False

End Sub

What's the problem, is it throwing an error? not working as expected? — CallumDA
This almost certainly isn't working as intended: ActiveSheet.Cells(1, Columns.Count).End(xlUp).Column. I think you should be using xlToLeft — CallumDA
You do specify a range on the 'Prospects' sheet. There is an error in specifying the LastColumn already pointed out above, but you don't use that variable. Then you hide columns on that sheet. If you want these columns hidden on the 'Results' sheet, why hide them in 'Prospects'? Your code snippet doesn't have a Copy command. Therefore it isn't apparent what you paste to the 'Result' sheet. But if your intention is to omit certain columns in the latter, hiding or deleting them should be done after the Paste and not on the original sheet's data. — Variatus
I thought I was doing the copy command with .copy, but this is the first VBA macro I've tried to do. It was copying over okay, but altering the original sheet. So what I ended up doing was copy all of sheet1 to sheet2 then do my filtering work on sheet2. — Diane

Malkill1 Malkill1 · Accepted Answer · 2017-05-14T04:48:41

First: Your title is confusing; do you want to filter the data on worksheet "Prospects", copy the visible data, and move it to the "Results" worksheet? Second: you "Dim r As Range" but you don't use it in your code. Third: you Don't Dim "LastRow" and "LastCol" and don't even use them in your code. Forth: Why are you filter "column A" then "filter Column M" before you hide the specific columns and u-nhide them? Fifth: your "LastCol" code is wrong Six: You hide and un-hide the columns for no apparent reason. Seventh: your "With code" does not make any sense, you are testing "sheet1", not copying anything and then pasting on "sheet1" not the "Results" sheet. which worksheet is "Sheets(1)"? I would suggest that you filter your data on the "Prospects" worksheet select the visible data using .SpecialCells(xlCellTypeV‌isible).Copy then paste to the "Results" worksheet

Copy data from one work sheet usiing criteria to another worksheet without changing original worksheet

2 Answers