ActiveCells

[PivotIdiot]

Hiya,

How do i give the activecell in the active sheet a reference or store the value to use within the macro without having to 'go and get it' again?

Cheers in advance,

Daniel

 

[njimack]

X = Activecell.Value

 

[VoG]

Hi. Try like this

Dim mycell As Range
Set mycell = ActiveCell

 

[PivotIdiot]

Many thanks!

 

[PivotIdiot]

X = Activecell.Value

Will this X 'reference' remain for ever more, can i change it by resetting the value then deleting it when im done. Or is it only valid when runnnig that particular macro?

 

[PivotIdiot]

how do i get this to work?

ActiveCell = X.Offset(3, 0).Select

 

[VoG]

If you mean that you want to select the cell 3 rows down

Dim X as Range
Set X = ActiveCell
X.Offset(3, 0).Select

 

[PivotIdiot]

I'll give that a whirl, many thanks.

 

[PivotIdiot]

Right then chaps & chapesses, I'm struggling!

Can someone tell me what i'm doing wrong here: -

Sub SORT_into_BRANCHES()
'
' SORT_into_BRANCHES Macro
'
Dim X As Range
Set X = ActiveCell

Sheets("INFO").Select
Range("AM").AutoFilter Field:=1, Criteria1:=X
Range("A9").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("RELATIONSHIPS").Select
X.Offset(3, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
ActiveWorkbook.Worksheets("RELATIONSHIPS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RELATIONSHIPS").Sort.SortFields.Add Key:=Range( _
"H18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("RELATIONSHIPS").Sort
.SetRange Range("H17:H25")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub

I know its this final section where i'm trying to sort the pasted values in ascending order, but i cant get it right.
It needs to work for the actual highlighted selection that has just been pasted, then i want to go back to 'X', and offset to the next cell on the right, until there isnt a cell with a value to the right of 'X'.
Does this make sense?

 

[PivotIdiot]

Not sure i'm allowed to do this, but bump?