Copying the last used cell only

[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

 

[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

 

[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

 

[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.

 

[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?