Excel Macro - Assistance required to modify existing Macro.

[MCCAFFREY]

I have a diary system in excel and in order to place a new entry in the
diary I press a button which has the following macro attached.

(My question is below the double-line)

-----------------------------------------------------
Sub InsertNewTimeRow()
'
' InsertNewTimeRow Macro
' Macro recorded 13/04/2006 by Robert Burt
'

'
Range("A10").Select
Selection.EntireRow.Insert
Application.Goto Reference:="BlankTimeRow"
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Range("A10").Select
Range("B10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("b10").Select
Application.CutCopyMode = False
End Sub
=================================

The above macro always inserts an additional row in row 10 of my diary.
That additional row has all of the formatting and formulae which is applied
to the named range "BlankTimeRow". Often I want to insert an additonal
diary entry in a different row (not row 10), but I do want that inserted row
(wherever it happens to be) to contain all of the attributes of
"BlankTimeRow". Can anyone assist me by telling me how to make another
macro which will do this for me?

For Info - "BlankTimeRow" is a row which lurks below the bottom of my diary
and it is always moving down the spreadsheet incrementally when a further
diary entry is clicked into existence by the above Macro.

 

[Roger Govier]

Hi Gerry

Assuming you always place your cursor in column A when making the
insert, the following should work for you.

Sub InsertNewTimeRow()
'
' InsertNewTimeRow Macro
' Macro recorded 13/04/2006 by Robert Burt
'
ActiveCell.EntireRow.Insert
Range("BlankTimeRow").Copy ActiveCell
ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[MCCAFFREY]

Thank you Roger. It worked well. How can I get a Pop-up to appear saying-
"Move cursor to column A and try again" on the assumption the user is not in
column A when the Macro is invoked.

Thanks

Gerry

 

[Gord Dibben]

' Macro recorded 13/04/2006 by Robert Burt

If ActiveCell.Column <> 1 Then
MsgBox "Select Column A and Re-try!"
Else

'rest of macro

End If
End Sub


Gord Dibben MS Excel MVP