Reference Cell in different Sheet w/o .Select

[Mantle51]

Hi again,

I am trying to avoid the .Select property as I need a value from one sheet, the activesheet pasted into another sheet. I tried something like this...

Sheets("5_New").Cells(Offset(,5))=Activecell.Value


Thanks

 

[SkipVought]

Hi,

Sheets("5_New").[b]SomeCellReference[/b].Offset(0,5).Value = Activecell.Value

Skip,

_[red][/red]

 

[Mantle51]

OK, but I cannot find that property which allows me to reference the cell with the focus in the non activesheet without knowing the row? (I've worked around using a cumbersome count routine, then adding to it and re-setting, but this is ugly) If you were to toggle to the sheet, the focus would always be in the correct row, I just don't know what that row is as the intervals between foci will be different with each iteration. Something along the lines of Current Region.Offset ... something that will place activecell.value in a cell 5 columns over from cell with focus.

Thank you

 

[Mantle51]

OK, i think i might've answered me own inquiry, as if anyone really cares. However, because at some point the non active sheet is the activesheet as this is how the focus is moved to subsequent row(using offset method), then it only makes sense to set a variable, tmpRow=activecell.row, after the method runs simply insert tmpRow into cell reference property like this....

Sheets("5_New").Cells(tmpRow,"U").Offset(0,5).Value = Activecell.Value

Thanks...
Hey don't mention it!