Automatically insert new line

[Gibbo_Steph]

Hi!

I have a sheet where I am recording attendance - lets say it looks something like this

Place	Day	Name
NYC	Monday
Miami	Tuesday

(however I have more columns between day and name)

Name is a drop down with a defined set of names. I want to be able to select a name and then it automatically inserts a new line that replicates the cells of the line above it with the exception of the drop down cell. for example something like :

Place	Day	Name
NYC	Monday	Cathy
NYC	Monday
Miami	Tuesday	Bob
Miami	Tuesday

Essentially multiple people might be in NYC on Monday, and multiple people might be in Miami on Tuesday. Because multiple people will be updating this sheet I am trying to have it so that all they have to do is select a name under the right event, then itll be ready for more entries without anyone else adding lines or generally touching anything haha

I am assuming this will be a macro but I am not familiar enough with coding within Excel to do it

TIA

 

[Joe4]

Welcome to the Board!

How many columns are in your list exactly (what are the first and last)?
And which column is the Name field actually in (is it field 3 or some other column)?

 

[Gibbo_Steph]

Hi !

I have A to I with the name drop down being in G

H and I are for different comments and will not be autopopulated (in terms of repeating the previous data)

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Get target row
    r = Target.Row
    
'   Only run if in column G under row 1
    If Target.Column = 7 And r > 1 Then
        Application.EnableEvents = False
'       Insert new row
        Rows(r + 1).Insert
'       Copy row above, from columns A - F
        Range(Cells(r, "A"), Cells(r, "F")).Copy Cells(r + 1, "A")
        Application.EnableEvents = True
    End If

End Sub

This should automatically do what you want, as you make a selection from column G.

 

[Gibbo_Steph]

Thank you ! It works perfectly.

 

[Joe4]

You are welcome.
Glad I was able to help!