Using variable in CopyToRange:=
 

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

Using variable in CopyToRange:=
 

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


"BrianR" wrote:

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

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

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

Using variable in CopyToRange:=
 

Check your other thread.

BrianR wrote:

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

--

Dave Peterson

Using variable in CopyToRange:=
 

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))

"Allllen" wrote:

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


"BrianR" wrote:

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

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

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