Help on copy and paste macro to next empty row

[sarath25]

Hi..

I want to copy the value of cell B21 (if not zero), to next empty cell in column B (after B26)and clear the cells B7:B8.

Pl. help me with a macro to do the above.

Thanks

[royUK]

How will you run the code?

  If Cells(21, 2) <> 0 Then
        Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Cells(21, 2)
        Range("B7:b8").ClearContents
    End If

[sarath25]

By assigning it as macro to a button.. Thanks

[royUK]

Then add the code that I gave to you to a button on the sheet

[sarath25]

Did, but get an error "Compile error; Invalid outside procedure"

[sarath25]

Pls. note, I have attached the sheet for u,

On clicking the save button,


I need the total value of B21 to be copied to the next empty row in after B26 and

entries in B7:B8 & B16:B20 to be cleared.

Thanks

[Leith Ross]

Hello sarath25,

Welcome to the Forum!

The following macro has been added to "Save" button in the attached workbook.

Sub SaveData()

  Dim C As String
  Dim Cell As Range
  Dim EmptyCell As Range
  Dim Rng As Range
  Dim Wks As Worksheet
  
    C = "B"
    Set Wks = ActiveSheet
    
    
    Set Rng = Wks.Range(Wks.Cells(26, C), Wks.Cells(42, C))
    
    For Each Cell In Rng
      If Cell = "" Then Set EmptyCell = Cell: Exit For
    Next Cell
    
    If EmptyCell Is Nothing Then
       MsgBox "There are No Empty Cells in Range " & Rng.Address
    Else
       EmptyCell.Value = Wks.Cells(21, C).Value
       Wks.Cells(7, C).Resize(2, 1).ClearContents
       Wks.Cells(16, C).Resize(5, 1).ClearContents
    End If
    
End Sub

[sarath25]

Thanks a lot dear, can an If condition added in the beginning to perform the macro only if B21 is not zero.

[JBeaucaire]

Just add this near the top:

    C = "B"
    Set Wks = ActiveSheet
    If Wks.Range("B1") = 0 Then Exit Sub

[royUK]

dOES THIS WORK?

Option Explicit

Sub CopyData()
If Cells(21, 2) <> 0 Then
        Cells(43, 2).End(xlUp).Offset(1, 0).Value = Cells(21, 2)
        Range("B7:b8,b16:b20").ClearContents
    End If
End Sub

[sarath25]

Thank you all for the kind help... I got it alright..

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save