Copying Unique Values from Non-Static Column 1

[Rzrbkpk]

I am attempting to copy all unique values from a specific column on a worksheet titled "Raw_Data" to a a worksheet titled "temp". This was working for a bit, but due to raw data input columns varying for multiple reports, I can't place a static column such as "I" in my code for the advanced filter. I've successfully managed to identify the needed column by header name(which will not change), but can't pass that variable into my advanced filter. I get a type mismatch. Any help would be appreciated.

    Sub Sample()
    
    Dim ws As Worksheet
    Dim lRow As Long, aCol As Long
    Dim aCell As Range
    Dim fRange As Range
    
    Set ws = Sheets("Raw_Data")        
        With ws
        Set aCell = .Rows(1).Find("Current Lead Case Manager")
        aCol = aCell.Column
        .Range(Cells(1, "aCol")).AdvancedFilter xlFilterCopy, , Sheets("Temp").Range("A1"), True
        End With
    
    End Sub

 

[PHV]

.Range(.Cells(1, aCol)).AdvancedFilter xlFilterCopy, , Sheets("Temp").Range("A1"), True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rzrbkpk]

Thanks PHV, but now I'm getting Run-time error '1004': Application-defined or Object-defined error.

 

[SkipVought]

Do you ACTUALLY have a sheet named "Temp"?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

Hi Skip!
Yes, the sheet I'm trying to paste to is labeled "Temp".

 

[SkipVought]

post your current code


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

This is with the correction PHV advised.

Sub Sample()
    
    Dim ws As Worksheet
    Dim lRow As Long, aCol As Long
    Dim aCell As Range
    Dim fRange As Range
    
    Set ws = Sheets("Raw_Data")
        
        With ws
        Set aCell = .Rows(1).Find("Current Lead Case Manager")
        aCol = aCell.Column
        .Range(.Cells(1, aCol)).AdvancedFilter xlFilterCopy, , Sheets("Temp").Range("A1"), True
        End With
    
End Sub

 

[SkipVought]

on what statement did the error occur?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

It's the following line that the error occurs:

        .Range(.Cells(1, aCol)).AdvancedFilter xlFilterCopy, , Sheets("Temp").Range("A1"), True

Specifically the cell reference is the issue. When I hover of the cell reference before executing that line, I get the error.

 

[SkipVought]

what is the value of aCol when this error occurs?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

The value aCol = 10 which corresponds to the column that contains the header "Current Lead Case Manager".

 

[PHV]

And this ?

.Cells(1, aCol).AdvancedFilter xlFilterCopy, , Sheets("Temp").Range("A1"), True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

try doing it MANUALLY.

Cant copy to another sheet!!!

I'd use MS Query. faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...or set up a Pivot Table on Temp for this data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

PHV - I get the same run-time error when I try that method.

Skip - Well that's no fun at all ...that and it disrupts the larger part of the rest of my code. I was able to to copy to another sheet as long as the column didn't change with the following code:

Sub Sample()
    
    Dim ws As Worksheet
    Dim lRow As Long, aCol As Long
    Dim aCell As Range
    Dim fRange As Range
    
    Set ws = Sheets("Raw_Data")
        
    With Sheets("Raw_Data")
        .Range("I1", .Range("I" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
    End With

End Sub

 

[PHV]

And this ?

With Sheets("Raw_Data")
  .Range(.Cells(1, aCol), .Cells(.Rows.Count, aCol).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rzrbkpk]

PHV - YES!!! Two days in a row you have made my headache go away. Thank you!