Insert a Row Issue

[Marhier]

Morning everyone, I hope you're well!
I'm looking for a bit of advice on an 'insert a row' code I've been working on.

I have a form which is a sequential list of schedules and I'm trying to do two things.

1) Insert a row below the selected cell, clone all the text from the cell above, but give it the next number sequentially.
2) Insert a row below the selected cell, clone all the text from the cell above, but keep the number the same, adding a "-1" at the end of it.

FYI - the list of sequential numbers is in column A

The code I've got works, but just copies the text from the above cell, keeping the schedule number the same.
I can't figure out how give one code the next sequential number, or the other adding a -1 at the end of the cloned schedule.

My code is as follows:

Public Sub CloneSchedule()
Dim myCell
Set myCell = ActiveCell
If MsgBox("Have you selected the correct schedule to clone?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Call DMSL_UnProtect
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    myCell.Offset(1, 0).Select
    Rows(ActiveCell.Row).Delete
    myCell.Offset(0, 0).Select
Call DMSL_Protect
Application.ScreenUpdating = True
End Sub

Public Sub SplitSchedule()
Dim myCell
Set myCell = ActiveCell
If MsgBox("Have you selected the correct schedule to split?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Call DMSL_UnProtect
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    myCell.Offset(1, 0).Select
    Rows(ActiveCell.Row).Delete
    myCell.Offset(0, 0).Select
Call DMSL_Protect
Application.ScreenUpdating = True
End Sub

Any advice would be greatly appreciated.
Thank you.
Regards
Martin

 

[Marhier]

No worries... I sussed it myself!

Public Sub CloneSchedule()
Dim myCell
Set myCell = ActiveCell
If MsgBox("Have you selected the correct schedule to clone?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Call DMSL_UnProtect
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    [B]ActiveCell.Offset(1) = ActiveCell + 1[/B]
    myCell.Offset(1, 0).Select
    Rows(ActiveCell.Row).Delete
    myCell.Offset(0, 0).Select
Call DMSL_Protect
Application.ScreenUpdating = True
End Sub

Public Sub SplitSchedule()
Dim myCell
Set myCell = ActiveCell
If MsgBox("Have you selected the correct schedule to clone?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Call DMSL_UnProtect
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    [B]ActiveCell.Offset(1) = ActiveCell.Value & "-A"[/B]
    myCell.Offset(1, 0).Select
    myCell.Offset(0, 0).Select
Call DMSL_Protect
Application.ScreenUpdating = True
End Sub

 

[MUKESHY12390]

I'm glad you found solution by your own.

there are several way to get next Order # below is one of them.

Sub Get_HigestValue()
Dim Hgtno As Long
Dim NextValue As Long
Dim myRng As Range


Set myRng = Worksheets("sheet1").Range("A:A") ''' Change range here...


Hgtno = WorksheetFunction.Max(myRng)


MsgBox "Current Highest Value: " & Hgtno


NextValue = Hgtno + 1




MsgBox "Next Number: " & NextValue
End Sub