Copying the last used cell only

I

Ibrahim Ozer

Hello All,

I`m the newest member of this group and hoping that some of you could
help me to resolve this problem.

I have a worksheet that contains data, let`s say Worksheet1, A column.
I have another worksheet (Worksheet2). Worksheet1, A column contains
names. Everytime I update it, I don`t delete anything; however, I add
a new name in the following cell in A column.

All I want is to copy the last name entered in Worksheet1, A column to
Worksheet2, A column, Cell1. Anytime new name added, Worksheet2, A
column, Cell1 will be updated.

I`ll really appreciate if you could help me with this.

Thanks v much
 
M

Mike H

Hi,

Put thisd in A1 of worksheet2

=INDEX(Worksheet1!A:A,MATCH(REPT("z",10),Worksheet1!A:A,1),1)

This will dispaly the last used text value in worksheet1 Column A.

Mike
 
M

Mike

Paste this into worksheet1 mod
Private Sub Worksheet_Deactivate()
Const colA = "A"
Dim sh1 As Worksheet
Dim sh1LastRow As Long
Set sh1 = Worksheets("Sheet1")
sh1LastRow = FindLastRow(sh1, colA)
Sheet2.Range("A1").Value = Range(colA & sh1LastRow).Value
End Sub

Private Function FindLastRow(whatSheet As Worksheet, whichCol As String) As
Long
'this finds and returns the actual last row on a sheet
'that has entry in specified column
'NOT the next row available for data entry
'so calling routine should add 1 to the returned value
'to determine next row available for new entry
'when it is found that a sheet has no entries, this
'routine will (properly) return zero.

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
FindLastRow = whatSheet.Range(whichCol & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 or later
FindLastRow = whatSheet.Range(whichCol &
Rows.CountLarge).End(xlUp).Row
End If
If FindLastRow = 1 And IsEmpty(whatSheet.Range(whichCol & "1")) Then
FindLastRow = 0 ' no entries at all in the column on the sheet
End If

End Function
 
I

Ibrahim Ozer

Hi,

Put thisd in A1 of worksheet2

=INDEX(Worksheet1!A:A,MATCH(REPT("z",10),Worksheet1!A:A,1),1)

This will dispaly the last used text value in worksheet1 Column A.

Mike
Thanks a lot!!!!

It worked well.
 
I

Ibrahim Ozer

Hello,

The following formula was working perfect; however, it doesn`t copy
the dates. I tried all other combinations of dates but it still
doesn`t work.

Any idea?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top