VBA Extract Unique Values with Advance Filter From One Worksheet to Another

Question

So I have this Workbook contains several worksheets.
In Sheet22 is a database and I need to extract unique values from one of its columns, which is Column "BC" (or Col 55). here is to illustrate the unique values that I want to extract:

I need to extract the unique values to another worksheet (Sheet 32) starts from Range A1. But before that, I need to clear the target range first if it is not empty, then after that, paste the unique values.

so this is the codes I used, (fyi I'm, still new to VBA, so I found this code to be the closest to what I have in mind and modified it):

Option Explicit

Sub uniqueValuesFromRangeCustomer()

    Dim rngCollectFrom As Range, targetRange As Range
    Dim lastRow As Long
    Dim sh As Worksheet

    Set sh = ThisWorkbook.Sheets("Sheet22")

    'Set the target range where the unique values will be copied to
    Set targetRange = Sheets("Sheet32").Range("A1")

    'Clear the target range if it is not empty
    If targetRange <> vbNullString Then
        'Find last row in target range column
        lastRow = Sheets("Sheet32").Columns(targetRange.Column).Find("*", , , , xlByRows, xlPrevious).Row
        'Delete target range
        Sheets("Sheet32").Range(targetRange, Cells(lastRow, targetRange.Column)).Delete xlUp
        'Reset target range (since it gets deleted)
        Set targetRange = Sheets("Sheet32").Range("A1")
    End If

    'Define the range the unique values will be extracted from
    'Find last row
    lastRow = Sheets("Sheet22").Columns(55).Find("*", , , , xlByRows, xlPrevious).Row
    'Set the source range
    Set rngCollectFrom = Sheets("Sheet22").Range(Cells(2, 55), Cells(lastRow, 55))

    'Use Advanced Filter
    rngCollectFrom.AdvancedFilter Action:=xlFilterCopy, copytorange:=targetRange, unique:=True

When I run the code, I got error message: "Subscript out of range", and when I debug, it highlights the code:

Set sh: ThisWorkbook.Sheets("Sheet22")

I still don't understand how to modify this code to works, please someone if can help me with this problem. Thanks before!

That means the workbook that contains the code does not have a sheet named "Sheet22" — BigBen, Aug 06 '20 at 17:45
@BigBen I was using code name instead of the worksheet name. The worksheet name is "Database", and the code name is "Sheet22", so there is a "Sheet22" in that workbook. — Agatha Rona, Aug 06 '20 at 17:54
You need to use the worksheet name. So, either `ThisWorkbook.Sheets("Database")`, or just `Sheet22`. — BigBen, Aug 06 '20 at 17:55
@BigBen OK, so I have changed them all to worksheet name. `Sheets("Sheet22")` become `Sheets("Database")` and also `Sheets("Sheet32")` become `Sheets("Summary")` , and that solves the error. But now, another error saying "Application-defined or object-defined error" and it highlights the code `Set rngCollectFrom = Sheets("Database").Range(Cells(2, 55), Cells(lastRow, 55))`. Do you know how to fix this? — Agatha Rona, Aug 06 '20 at 18:21
[Qualify the worksheet the `Cells` are on](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet). — BigBen, Aug 06 '20 at 18:22
@BigBen I have added `Set sh1 = ActiveWorkbook.Sheets("Database")` and `Set sh2 = ActiveWorkbook.Sheets("Summary")`. And qualify the worksheets to `Set rngCollectFrom = sh1.Range(Cells(2, 55), Cells(lastRow, 55))`. But still error, it gives "Method 'Range' of object '_Worksheet' failed". on that line of code. How to qualify this code the right way? — Agatha Rona, Aug 06 '20 at 19:32

VBA Extract Unique Values with Advance Filter From One Worksheet to Another

0 Answers0