excel vba search cell above keyword to copy and replace cell below another keyword

  • I need help to create a vba code for excel that will make my everyday work easier. I need to find the keyword "Submissions" and take the cell right above it, copy the cell.

    Paste the cell below another keyword "Totals". And loop it such that it goes on finding the next "Submissions" and "Totals"

    I'm not taught in vba. I usually just copy and paste codes I've found on the net. My code may be messy and all over the place

    This code I have is not working, it uses the cell found above the first keyword "Submissions" and paste on cells below all the "Totals", which is not what I want. I want the value on cell (a date) above first "Submissions" on cell below first "Totals" and value on cell above second "Submissions" on cell below second "Totals" and continue.. They are all in the same column (column A)

    [VBA]Sub Macro1()
    ' Macro1 Macro

    Application.ScreenUpdating = False

    Dim x
    Dim y

    For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
    For y = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1

    If Cells(x, 1) = "Submissions" Then
    If Cells(y, 1) = "Totals" Then

    ActiveCell(x - 1, 1).Copy ActiveCell(y + 1, 1)

    Application.CutCopyMode = False
    End If
    End If
    Application.ScreenUpdating = True
    End Sub[/VBA]

    Any help would be appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!