none
Insert a new row and copy values when a cell is completed RRS feed

  • Question

  • Hi, 

    I am trying to create an excel with two sheets. I will be completing the first one every time a new person comes. But I want that the second sheet would be completed automatically (copying the first name and last name) every time I completed a row in the first sheet. I will be only completing the date manually in this second sheet. I would also like that this second sheet would have the information in opposite order (I mean, the newest record would be in the top)

    I leave an example below:

    I want to add to the record Amanda Villoria. After completing manually the first name, last name, age and country, the values Amanda and Villoria will be automatically copied in the second sheet in the top and I would be adding manually the date value. 

    Sheet 1

    First Name Last Name Age  Country
    Todd Muller 25 Canada
    Ann Schlum 30 Germany
    Juan Perez 24 Argentina
    Amanda Villoria 39 Venezuela

    Sheet 2

    First Name Last Name Date
    Amanda Villoria 11-03-19
    Juan Perez 07-03-19
    Ann Schlum 03-03-19
    Todd Muller 01-03-19

    Thank you in advance

    Monday, March 11, 2019 11:30 PM

All replies

  • Hi, 

    I am trying to create an excel with two sheets. I will be completing the first one every time a new person comes. But I want that the second sheet would be completed automatically (copying the first name and last name) every time I completed a row in the first sheet. I will be only completing the date manually in this second sheet. I would also like that this second sheet would have the information in opposite order (I mean, the newest record would be in the top)

    I leave an example below:

    I want to add to the record Amanda Villoria. After completing manually the first name, last name, age and country, the values Amanda and Villoria will be automatically copied in the second sheet in the top and I would be adding manually the date value. 

    Sheet 1

    First Name Last Name Age  Country
    Todd Muller 25 Canada
    Ann Schlum 30 Germany
    Juan Perez 24 Argentina
    Amanda Villoria 39 Venezuela

    Sheet 2

    First Name Last Name Date
    Amanda Villoria 11-03-19
    Juan Perez 07-03-19
    Ann Schlum 03-03-19
    Todd Muller 01-03-19

    Thank you in advance

    Try the below code.

    Dim LastRow As Integer
    
    LastRow = ThisWorkbook.Sheets("Sheet 1").Cells.SpecialCells(xlCellTypeLastCell).Row
    '******This returns Last row of sheet 1, since you need to copy FirstName/LastName from last row of sheet 1
    
    ThisWorkbook.Sheets("Sheet 2").Rows(2).Insert
    '******Insert row, now Sheet 2 Row 2 is empty
    '******In your case "Sheet 2" is the Sheetname, make the adjustment if your sheetname is different.
    '*******In your case, Rows(2), 2 means: the row is inserted above row #2.
    
    Range(ThisWorkbook.Sheets("Sheet 2").Cells(2, 1), ThisWorkbook.Sheets("Sheet 2").Cells(2, 2)).Value = Range(ThisWorkbook.Sheets("Sheet 1").Cells(LastRow, 1), ThisWorkbook.Sheets("Sheet 1").Cells(LastRow, 2)).Value
    '*******Copy FirstName/Lastname from Sheet 1 Last Row of Column A/B   to Sheet 2 cells A2:B2. Assuming First Name is in cell A1, if not, make the adjustment.
    
    ThisWorkbook.Sheets("Sheet 2").Cells(2, 3).Value = Format(Now, "dd-mm-yy")
    '**********Put down today's date in Sheet 2 cell B3, in your case dd-mm-yy is your date format






    • Edited by VA_er Tuesday, March 12, 2019 12:48 AM
    Tuesday, March 12, 2019 12:19 AM