asked on 11/9/2017

insert new row above with formatting is cell value >00:01

hi,
I would like vba code to automatically insert a new row above if the value in cell H9 is greater than 00:01 (time format). So the current row 9 becomes row 10. I would like the formatting from current row 9 to be included with the insert.
thanks in advance

VBA

Last Comment

Sarah

1/19/2018

SOLUTION

Ejgil Hedegaard

11/10/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Sarah

11/11/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Ejgil Hedegaard

11/11/2017

Yes it does.
Here is a version trigged when you enter a time in D9.
It copies the old row 9, and moves to C9 for the next entry.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D9")) Is Nothing And Target.Count = 1 Then
        If Range("H9") > 1 / (24 * 60) Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            Rows(9).Insert
            Range(Cells(10, 1), Cells(10, Cells.SpecialCells(xlCellTypeLastCell).Column)).Copy Range("A9")
            Application.CutCopyMode = False
            Range("C9").Select
            Application.EnableEvents = True
        End If
    End If
End Sub

Open in new window

Sarah

11/11/2017

ASKER

hi - I still can't seem to get it to work. I suspect it's probably me but file attached if you wouldn't mind taking a look. thanks in advance
11.11.17.xlsm

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

11/11/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Sarah

11/12/2017

ASKER

thank you so much. I have lots to learn. really appreciate your help! thanks Sarah

Sarah

11/12/2017

ASKER

Thank you so much. working perfectly!

Sarah

1/17/2018

ASKER

hi Ejgil,
Not sure if you will see this but I have an issue with the previously working file? the new row is now being inserted when the start and end times are entered now whereas previously the new line would insert on completion of the full row 9 on Sheet2. Would you be able to take a look at the file an advise what has happened? And if there is any way to prevent a similar occurrence?
many thanks again
Sarah
Time-Tracker-December-2017-Start-17.xlsm

Ejgil Hedegaard

1/17/2018

You have not used the latest version of the code.
It is the code in the worksheet module for Data Entry that does the work.
There is an almost similar, older version, in Module 10, and you have probably copied that.
I have deleted Module 10, because the event handling code can't work there.
There are several modules without code, you should delete them.
Time-Tracker-December-2017-Start-17.xlsm

Sarah

1/19/2018

ASKER

thanks Ejgil - I thought I had checked back to the latest version but must have done something wrong. thanks again. :-)

insert new row above with formatting is cell value &gt;00:01

insert new row above with formatting is cell value >00:01