For a value

You asked for the value this time, so for a value:

Sub PopCell1()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Value = rgLastCell.Value
End Sub

for a formula

Sub PopCell2()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom
i want to take the value that we found (the last cell in column1) and
insert
it after the last filled cell in a different column in a different sheet.

can you help me with that.

Thanks alot.

Hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell
address.
so
that if the contents change, it will be updated in the target cell.

2) can we also do the same thing you did to the current cell to the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last
request.

in the code you gave me i have to specify which cell to put the
results
into. can we make the code such that it puts the address of the
cell
that
is
selected in the spreadsheet automatically? rather than me changing
the
code
everytime.

or....is it possible that excel asks me to which cell i want to
put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there,
do
Insert=Module
Paste the code in the module that is inserted. Change the code
to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into
the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is
column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell
in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell
in a
column?

For exmaple I have col A and in that column first five
cells
filled up
and
then next three cells are empty and then again next
five
are
filled
up. I
need to find the last cell in the column which is
filled
up.

Help would be appreciated.

Thanks in advance.