run vb code when its time for new record

[xluser26]

Hello,

I created a macro that inserts a copy of the last row's format to make a new record (row). I would like to some how run the macro without a shortcut key.

Instead I would like to be at the last record (row) and once I start typing something on any cell in the next empty row it will create the new row, or run the macro.

I would like to link or integrate the following macro to the new vb code.
Note: If you have any suggestions on a better way to code the creation of a "New Record (ROW)" i would really appreciate it.

Sub RelativeAddNewRow()
'
' RelativeAddNewRow Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlUp).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Select
End Sub

Thank You,

xluser26

 

[HalfAce]

Hello xluser26, welcome to the board.
Here's an idea you can try. I didn't base this off of the sheet change or the selection change event, but the before double click event instead. This gives the user a bit more control on when the code gets executed, rather than have it execute with every single change (or selection change) whether you want it to or not.
At any rate, you can give this a try by right clicking the sheet tab, choosing View Code and pasting this in the sheet module.
Note it depends on you having data in column A all the way down as far as your data goes. If we need to change that we can very easily. What it does is simply copy the last row containing data in column A and pastes the format(s) of that row to the blank row below it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'''Only work if column A gets double clicked
If Target.Column <> 1 Then Exit Sub

'''Only work if the double clicked cell is blank
If Not Target = "" Then Exit Sub

'''Copy the last row containing data in column A and _
   paste the format(s) to the row below it.
Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy
Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Hope it helps.

Oh yeah, once you're done, the user just double clicks a blank cell in column A, (the code executes) and they can start typing.

 

[xluser26]

Half Ace,

The code works very well and makes things much cleaner and effecient. Thank You.

I've been trying to integrate my other code with it and Im having trouble doing it right. Can you please give me a hand.

Before I used to add data in a cell C4, then it generated a date (B1) and a Record Id (A1). I seperated the Date code to work by itself, but I would like the Record Id added to the New Record (row) in cell Column A.

Here is what its looking like....

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'''Only work if column A gets double clicked
If Target.Column <> 1 Then Exit Sub
'''Only work if the double clicked cell is blank
If Not Target = "" Then Exit Sub
'''Copy the last row containing data in column A and _
   paste the format(s) to the row below it.
Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy
Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
 
 
'-------------------------- Add Record Id --------------------------Const NumQuarters As Long = 10000
If Target.Column = 1 Then
    Application.EnableEvents = False
    If Target.Offset(, 0).Value = vbNullString Then
        Target.Offset(, 0).Value = NumQuarters
        If Target.Offset(, 0).End(xlUp).Row = 1 Then
            Target.Offset(, 0).Value = NumQuarters
        Else
            Target.Offset(, 0).Value = Target.Offset(, 0).End(xlUp).Value + 1
        End If
    End If
    Application.EnableEvents = True
End If
End Sub
' -----------------------------Add Record Id >>>End Code>>>>

it generates the 100000 in the first record, but its not increasing the number on the next rows, just duplicates.

Thank You,

xluser26

 

[xluser26]

Ahhh, its working again. This is great. Thanks again HalfAce... I figured out the problem, it was my code, not the integration with the new code.

Have a Happy New Year!!

 

[HalfAce]

Sorry for the delay. Glad to see you got it all worked out.
Just one comment on the code. (only as an FYI...)
Your statements 'Target.Offset(, 0)' are really saying 'Target.Offset(zero rows down, and zero columns to the right...' - so in reality, you could simply refer to it as 'Target' and do away with the offsets.
It won't hurt anything to leave it the way you have it now, just adds a nano-second or two to the execution time.
(And you never know when you might be in a hurry...)

Hope it helps, and you too, have a happy & safe New Year.

 

[xluser26]

Thanks for the heads up, ill make the changes so the program doesn't run like its rush hour.

xluser26