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
SarahAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Insert this code in the worksheet module.
It expect you type in H9.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H9")) Is Nothing And Target.Count = 1 Then
        If IsNumeric(Range("H9")) 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").PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
                Range("H9").Select
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

Open in new window

SarahAuthor Commented:
hi thanks for the response and sorry for the delay in coming back.
I've tried the insertion of the code but nothing seems to be happening.
H9 is a formula based cell which shows the time calculated when a start (C9) & end (D9) time is entered so you don't physically type anything into that cell. Will this impact the code?
thanks
Sarah
Ejgil HedegaardCommented:
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

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

SarahAuthor Commented:
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
Ejgil HedegaardCommented:
The code is in Module10, but it has to be in the worksheet module for sheet "Data Entry".
It is a worksheet change event, meaning it runs on every change on the sheet.
So to work on sheet changes, it must be in the code module assigned to the sheet.
To avoid runs when the code change the sheet, the event handling is disabled by "Application.EnableEvents = False", and enabled again at the end.

The row is inserted when all cells in B9:G9 has a value, and H9>00:01.
All values B9:G9 are cleared for next entry, and B9 made the active cell.
I have changed the formula in column H.
Sarah-11.11.17.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SarahAuthor Commented:
thank you so much. I have lots to learn. really appreciate your help! thanks Sarah
SarahAuthor Commented:
Thank you so much. working perfectly!
SarahAuthor Commented:
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 HedegaardCommented:
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
SarahAuthor Commented:
thanks Ejgil - I thought I had checked back to the latest version but must have done something wrong. thanks again. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.