Not sure how to Amend current VBA code to do one more step

[keilah]

Hi Experts

i have the following VBA code.....not sure how thsi can be done,,,,but i am sure it'll be a walk in the park for some of you people....

here is the code:

Sub MyMacro()

Range(InputBox("Enter the end row i.e B37")).Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.RowHeight = 12.75
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

The question.......

The macro works prefectly fine.....however, i need it, once it has copied and pasted the contents of the row above the last row down one row,,,,,,,,if this makes since.....

to change the date field in column B (all column B) to the following month.....

so if say the date in row 40 the last row (minus one was June 06) as asked in the macro, then once the macro copies and pastes down one the contents of the above row......change the date in row 39 (i.e. row 40 - 1) to the following month.....

any questions you are not sure of just ask...

thanks

[Bob Phillips]

[vba]

Sub MyMacro()
Dim cell As String

cell = InputBox("Enter the end row i.e B37")
With Range(cell)
.EntireRow.Insert Shift:=xlDown
.RowHeight = 12.75
.Offset(-2, 0).EntireRow.Copy
.Offset(-1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Offset(-1, 0).Value = DateSerial( _
Year(.Offset(-2, 0).Value), Month(.Offset(-2, 0).Value) + 1, Day(.Offset(-2, 0).Value))
End With
End Sub
[/vba]

[keilah]

Thanks you

hale XLD