Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Auto-fill column when inserting a new row?

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Auto-fill column when inserting a new row?

    I've got a spreadsheet with a hidden 'start date' column. The start date is going to be the same for all tasks that are inserted into the sheet, so I want it to automatically fill this date in when people insert a new row.

    Is this possible??

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    When they insert a new row, are they entering in data somewhere?
    If so, then one way to do this would be when they enter something in a certain column, it automatically checks the value in another column, and if that column is blank, populate it with the desired data.
    Does that sound like it would work?

    If so, please provide us with the following:
    - What column do you want "triggering" this to run when updated?
    - What column is supposed to be updated?
    - What date goes in this column (today's date, or some other date)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Auto-fill column when inserting a new row?

    Quote Originally Posted by Joe4 View Post
    When they insert a new row, are they entering in data somewhere?
    If so, then one way to do this would be when they enter something in a certain column, it automatically checks the value in another column, and if that column is blank, populate it with the desired data.
    Does that sound like it would work?

    If so, please provide us with the following:
    - What column do you want "triggering" this to run when updated?
    - What column is supposed to be updated?
    - What date goes in this column (today's date, or some other date)?
    Yes that sounds like it will work! Thank you! I would upload a screenshot but for some reason I can't do it on my computer.

    So, I have a "Start Date" column (column B) that will need to be automatically updated with the same date each time. This is 01/09/2019.

    The column that other data is being entered into will be the "End Date" (column C), so that deadlines/duration of tasks can be calculated etc.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    Right-click on the sheet tab name at the bottom of the screen, select "View Code", and then paste this VBA code in the resulting VB Editor window:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
        
    '   See if updates cell in column C
        Set rng = Intersect(Target, Columns("C:C"))
        If rng Is Nothing Then Exit Sub
        
    '   Loop through updated cells in column C
        For Each cell In rng
    '       Exclude title row
            If cell.Row > 1 Then
    '           Populate column B with date
                If cell <> "" Then
                    Application.EnableEvents = False
                    cell.Offset(0, -1) = DateSerial(2019, 9, 1)
                    Application.EnableEvents = True
                End If
            End If
        Next cell
        
    End Sub
    This will automatically populate column B when column C is populated (skipping row 1, assuming title row).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    Amazing! Thank you so much!!!

    If I wanted to apply this to other worksheets but instead of copying the date it copied the formula, how would I go about it?

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    If I wanted to apply this to other worksheets but instead of copying the date it copied the formula, how would I go about it?
    I am not sure I understand what you are saying about a formula...
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    So I have another sheet with a table that has these headings:

    Project -- Project Owner -- Days Until Deadline -- Project Support

    In project owner, days until deadline and project support there are formulas so that the table reads from a Master sheet and pulls the information through, so that it will automatically change when changed on the Master. I am trying to have it so that when someone inserts a new row to enter a new project, these formulas automatically appear in the new row.

    Does that make sense or have I confused it even more?

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    Cross posted multiple sites.

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

    Please provide links to all other sites where you have asked this question.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    Please provide links to ALL the other places where you have posted this question.
    I do not wish to waste time creating code for this if the question has already been answered somewhere else.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-fill column when inserting a new row?

    Apologies!!! New to this and tried to post earlier but didn't work for some reason.

    Here are the links:

    https://www.excelforum.com/excel-pro...ml#post5196878

    It is also posted in the Reddit Excel forum but I cannot get access to it right now so will add it in once I have got it.

    This is no longer an problem as I have decided not to pursue it and just stick with dragging down the cells.

    I am very appreciative of your help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •