Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

Excel to find last cell in column empty and copy name to another cell

Hi Experts,
I have a workbook that I need help with.
When you enter a new name to the last cell in the last row of a column in MasterLog, I want that name to copy to another cell in the second sheet. I would like to copy the name for a total of 5 times. Both sheets have data in many other columns too. This is the dummy WB for you. (no color formatting needed, I just colored it to help understanding)
If we can do this in a formula that would be great. Is it possible in formula?
Have a look here.help01.JPG help02.JPGDummyCopyLastEntry.xlsx
0
chris pike
Asked:
chris pike
  • 6
  • 5
  • 2
  • +1
2 Solutions
 
aikimarkCommented:
Put this code into the Master Log worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    Dim rng As Range
    Dim lst As ListObject
    Dim strName As String
    Set wks = ActiveSheet
    Set lst = wks.ListObjects(1)
    Debug.Print wks.ListObjects.Count
    If Intersect(Target, lst.Range) Is Nothing Then
    Else
        strName = lst.ListRows(lst.ListRows.Count).Range.Value
        Set wks = Worksheets("ExpDate")
        Set rng = wks.Cells(wks.Rows.Count, 2).End(xlUp)
        wks.Range(rng.Offset(1), rng.Offset(5)).Value = strName
    End If
End Sub

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this....

Place the following code on Master Log Sheet Module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim sws As Worksheet, dws As Worksheet
Dim tbl1 As ListObject, tbl2 As ListObject
Dim slr As Long, dlr As Long
Set sws = Sheets("Master Log")
Set dws = Sheets("ExpDate")
Set tbl1 = sws.ListObjects(1)
Set tbl2 = dws.ListObjects(1)
slr = tbl1.Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
dlr = tbl2.Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
On Error GoTo SkipError
If Not Intersect(Target, sws.Cells(slr, tbl1.Range.Columns(1).Column)) Is Nothing Then
   Application.EnableEvents = False
   If Target <> "" Then
      dws.Cells(dlr, tbl2.Range.Columns(1).Column).Resize(5).Value = sws.Cells(slr, tbl1.Range.Columns(1).Column)
   End If
   Application.EnableEvents = True
End If
SkipError:
Application.EnableEvents = True
End Sub

Open in new window

For details, refer to the attached.
DummyCopyLastEntry.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@aikimark
If I am not wrong, your code will be triggered if any name in the table column is changed and it will copy the last name to the ExpDate sheet each time the code is triggered.
I am not sure if that is the original requirement of the question.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
Hi Chris

I was looking at a formula solution using INDEX function on source table but had to dash for a train. I will look again later when I am back at a pc.

Thanks
Rob
0
 
chris pikeAuthor Commented:
@aikimark
Neerraj is correct, it duplicates last entry every time there is a change. This does not work, Neeraj's solution works great.

Neeraj, could you help me with putting in notes into the VB so I can make this work with my real sheet.
I need where to change table, etc  name..... I need to edit it easily.
It works great but having hard time putting into my working WorkBook
Thanks so much.
Chris
0
 
aikimarkCommented:
Good catch, Neeraj.

This will look for a change to the last cell in the table.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    Dim rng As Range
    Dim lst As ListObject
    Dim strName As String
    Set wks = ActiveSheet
    Set lst = wks.ListObjects(1)
    Debug.Print wks.ListObjects.Count
    If Intersect(Target, lst.ListRows(lst.ListRows.Count).Range) Is Nothing Then
    Else
        strName = lst.ListRows(lst.ListRows.Count).Range.Value
        Set wks = Worksheets("ExpDate")
        Set rng = wks.Cells(wks.Rows.Count, 2).End(xlUp)
        wks.Range(rng.Offset(1), rng.Offset(5)).Value = strName
    End If
End Sub

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with the comments added so that you can tweak it as per your requirement.
DummyCopyLastEntry.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@aikimark
Thanks!
0
 
chris pikeAuthor Commented:
@Neerraj
There is only one table on each sheet.
I am trying to change the column numbers now.
Master Log is named Table4
ExpDate is named TableExpDate

I tried your code, but can not get it to work,
This is what Master Log sheet looks like.
Column "C" on Master Log is the column I will add the names.
help03.JPG
On ExpDate Column "B" is the column to copy the names.
help04.JPG
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Change the sheet names in the following lines of code...
Set sws = Sheets("Master Log")
Set dws = Sheets("ExpDate")

Open in new window


The following line of code assumes that the Names are in column 1 of the Table on Master Log Sheet.
tbl1.Range.Columns(1)

Similarly the following line of code assumes that the Names are to be added in column 1 of the table on ExpDate sheet.
tbl2.Range.Columns(1)

Please note that the highlighted number represents the columns in table not the columns of the sheet.
So if the names are in column 3 of the Table on Master Log sheet, you will have to replace all the instances of tbl1.Range.Columns(1) with the following line......
tbl1.Range.Columns(3)

The same changes you will have to make for table column reference for the table on ExpDate sheet as well.
0
 
chris pikeAuthor Commented:
Thanks Neeraj, Do I need to change the table names?
I changed tbl1.Range.Columns(1) for MAster log to column# 3
I didnt have to change EXP as that one is already column #1 of the table.
Still cant get it to work,
Thanks
Chris
0
 
chris pikeAuthor Commented:
Thank you very much.
Aikimark's solution works for my dummy sheet so I awarded him 100/

Thank you very much Neeraj for your help, always a pleasure.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Chris! Glad to help.
0
 
Rob HensonIT & Database AssistantCommented:
I know you have already accepted a VBA solution but here is the formula solution as promised.

Formula in B4 of ExpDate:

=INDEX(Table1[Column1],CEILING(COUNTA(B$3:B3)/5,1),1)

Open in new window

Copy down as far as required, used in a table it should auto complete as new rows are entered in the table.

Note the $ to fix the first row of the count, as copied down this will expand the count range. Dividing the count by 5 will give a decimal value ending in a factor of 0.2; using CEILING with a factor of 1 will then round this up to the next integer.

So the first 5 entries give a count of 1 to 5 which divided by 5 give a decimal of 0.2, 0.4, 0.6, 0.8 and 1, when rounded up all become 1 so the INDEX uses the first entry in the list. The next 5 give a count of 6 to 10 which divided give 1.2, 1.4, 1.6, 1.8 and 2 which all rounded up give 2 so the INDEX uses the second entry in the list and so on.

Thanks
Rob
0
 
chris pikeAuthor Commented:
@Rob
Wow, you guys are amazing,
Rob I was able to make yours work in ,my working program. It's weird how sometimes you cant just use the table name or column name, but you have to actually click on the sheet name first then the column name to link the formula up properly. I don't get why this works something and sometimes you can just plug in the name into the formula and everything works. But, I was able to fiddle and make it work.
Awesome work....I saved the formula, and will use it for sure in future projects.
Thanks
Chris
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now