Using variable in CopyToRange:=

[BrianR]

I have the following code to extract unique values from a column of data. I
need to replace the "O2" with a variable. Does this field only allow " " and
not variables?

BrianR

'extract unique IDs from column A
With Sheets(Sheet)
Range("A2:A" & last_row_used_local).AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=.Range("O2"), Unique:=True

'sort the unique IDs
.Range(.Range(store_list_column),
..Range(store_list_column).End(xlDown)) _
.Sort Key1:=.Range("O2"), Order1:=xlAscending, Header:=xlYes
End With

 

[Allllen]

No, that should work fine.
Try this as a first step.
You will have to determine how you are going to set that range.

--
Allllen

I have the following code to extract unique values from a column of data. I
need to replace the "O2" with a variable. Does this field only allow " " and
not variables?

BrianR

Dim myrange as range '****changed
Set myrange = range("O2") '****changed

 

[Dave Peterson]

Check your other thread.

I have the following code to extract unique values from a column of data. I
need to replace the "O2" with a variable. Does this field only allow " " and
not variables?

BrianR

'extract unique IDs from column A
With Sheets(Sheet)
Range("A2:A" & last_row_used_local).AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=.Range("O2"), Unique:=True

'sort the unique IDs
.Range(.Range(store_list_column),
.Range(store_list_column).End(xlDown)) _
.Sort Key1:=.Range("O2"), Order1:=xlAscending, Header:=xlYes
End With

 

[BrianR]

The "O" in my address is actually being passed in the subroutine as an
integer. I tried this but I'm getting a runtime 1004 error.

Set DestCell = Sheets(Sheet).Range(Cells(2, ID_List))