CODEWITHSUNDEEP
vbaexcel
collins osei
collins osei

Reputation: 3

Creating a code for Moving onto Next Row upon Clicking on Button

I am creating a macro where upon clicking on Button1, the selection(DateofRequest) would move to cell C6 and PayeesName on Cell D6; another click when the name is entered move to C7 and D7 respectively and so on and so forth until all the cells are filled.

I will appreciate any help on this. Thank you!

Public Sub CommandButton1_Click()

    Dim DateofRequest As Date
    Dim PayeesName As String

    Worksheets("C Check Request").Select
    DateofRequest = Range("C13")
    Worksheets("C Check Request").Select
    PayeesName = Range("C15")

    Worksheets("Costume").Select
    Worksheets("Costume").Range("C5").Select
    RowCount = Worksheets("Costume").Range("C5").CurrentRegion.Rows.Count

    With Worksheets("Costume")
        RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(RowCount, 3) = DateofRequest
        .Cells(RowCount, 4) = PayeesName 
    End With

End Sub

Upvotes: 0

Views: 783

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You are trying to increase the count in columns C and D but are looking for the last row in A.

Also your code can be shortened by getting rid of the selects and activates. Also you can assign the values directly instead of using variables.

Public Sub CommandButton1_Click()
    Dim RowCount as Long
    With Worksheets("Costume")
        RowCount = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
        .Cells(RowCount, 3) = Worksheets("C Check Request").Range("C13").Value
        .Cells(RowCount, 4) = Worksheets("C Check Request").Range("C15").Value
    End With
End Sub

Upvotes: 2

Related Questions