Problem with auto increment

[mrcyvr]

Hello

Im trying to get my code to move to the next line when the command button is clicked. But the problem is, the code inserting a row everytime not just moving to the next row. I know its a problem with 'Entire Row.Insert' but what syntax do I replace it with to just move to the next row and not insert a row?

Private Sub COMMANDBUTTON1_CLICK()

    ActiveCell.Offset(Selection.Rows.Count, 0).EntireRow.Insert
    Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy
    Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats)
    Range("A" & ActiveCell.Row).Value = Range("A" & ActiveCell.Offset(-1, 0).Row).Value + 1
    Application.CutCopyMode = True
End Sub

 

[Tom Urtis]

The code is inserting a row because the first line in that code instructs that.
You can replace it with
activecell.Offset(1).Activate
Not sure if the rest of your code will work, but for sure with the above code line, the active row will be the next row down from where the active cell was when you execute the macro.

 

[mrcyvr]

Excellent, that worked. Only problem is, its supposed to increment the value each time, but is no longer doing that. Any idea why? The Value + 1 part is still there. Here is the revised code thus far

Private Sub COMMANDBUTTON1_CLICK()

    ActiveCell.Offset(1).Activate
    Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy
    Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats)
    Range("A" & ActiveCell.Row).Value = Range("A" & ActiveCell.Offset(-1, 0).Row).Value + 1
    Application.CutCopyMode = False
End Sub

The code is inserting a row because the first line in that code instructs that.
You can replace it with
activecell.Offset(1).Activate
Not sure if the rest of your code will work, but for sure with the above code line, the active row will be the next row down from where the active cell was when you execute the macro.

 

[mrcyvr]

I fixed it - here is the revised code

Private Sub COMMANDBUTTON1_CLICK()
    ActiveCell.Offset(1).Activate
    Range("A" & ActiveCell.Row).Value = Range("A" & ActiveCell.Offset(-1, 0).Row).Value + 1
    Application.CutCopyMode = False
End Sub

Excellent, that worked. Only problem is, its supposed to increment the value each time, but is no longer doing that. Any idea why? The Value + 1 part is still there. Here is the revised code thus far

Private Sub COMMANDBUTTON1_CLICK()

    ActiveCell.Offset(1).Activate
    Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy
    Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats)
    Range("A" & ActiveCell.Row).Value = Range("A" & ActiveCell.Offset(-1, 0).Row).Value + 1
    Application.CutCopyMode = False
End Sub

 

[Tom Urtis]

Thanks for the follow-up.
With the revised code as you posted it, you can omit the line
Application.CutCopyMode = False

 

[mrcyvr]

Great!

Thanks for the follow up. I omitted the line. I have one more thing Im doing now that is part of this project,
which you can see by the code. A msg box is triggered asking,
"how many unique numbers do you want to generate". If the user enters 15, that 15 should be stored as a variable, another msg box will appear asking,
"what number should we begin at?"

If the user enters the number 1,000. Then starting in
A1, 1000 will appear,
A2, 1,001
A3, 1,002
Is that possible to do? Im going to work on it, and post here with my troubles. Unless perhaps you or anyone has ever come across a script that can do that.

Sub Values_()
Dim Values As Integer
Dim Msg As String

Msg = "Hello, how many unique Values would you like to generate today?"
Values = InputBox(Msg)
ActiveSheet.Range("A1").Value = Values
End Sub


Private Sub COMMANDBUTTON1_CLICK()

    ActiveCell.Offset(1).Activate
    Range("A" & ActiveCell.Row).Value = Range("A" & ActiveCell.Offset(-1, 0).Row).Value + 1
    End Sub

Thanks for the follow-up.
With the revised code as you posted it, you can omit the line
Application.CutCopyMode = False