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

Sarah
Sarah used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

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
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

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

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
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

Author

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

Author

Commented:
Thank you so much. working perfectly!

Author

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
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today