VBA look for cell next to first blank, copy and paste

Hi there,


I need a vba code that looks in a particular sheet, in a particular column for the first unpopulated cell, then copies the value of the first cell on the right of the blank cell just found, and finally pastes the copied value to the first blank cell in another column in another worksheet.


I have been trying for some time, but not luck, would appreciate you help. Thank you.

I wouldn't look in the Test forum. Try the Excel Help Forum

Thank you

Code:

---

Sub CopyToAnotherPlace()
Dim Src As Range
Dim Dest As Range
  Set Src = RightOfFirstBlank(Sheets("Source WorkSheet Name"), Column Number)
  Set Dest = NextEmptyCell(Sheets("Destination WorkSheet Name"), Column Number)

  Src.Copy Dest
End Sub

---

Code:

---

Sub CopyToAnotherPlace_2()
  RightOfFirstBlank(Source Sht, Column Number).Copy NextEmptyCell(Destination Sht, Column Number)
End Sub

---

Code:

---

Function RightOfFirstBlank(Sht As WorkSheet, ColumnNumber As Long) As Range
'This uses the sheet, If you have more than a few thousand Rows before any blanks, it would
'  be worth it to use an array herein.

Dim Rw As Long

With Sht
  For Rw = 1 to .Cells(Rows.Count, ColumnNumber).End(xlUp).Row + 1
      If .Cells(Rw, ColumnNumber) = 0
        Then Set RightOfFirstBlank = .Cells(Rw, ColumnNumber + 1)
        Exit For
      End If
  Next
End With
End Function

---

Code:

---

Function NextEmptyCell(Sht As WorkSheet, ColumnNumber As Long) As Range
  With Sht
      If .Cells(1, ColumnNumber) = 0 Then
        Set NextEmptyCell = .Cells(1, ColumnNumber)
      Else: Set NextEmptyCell = .Cells(Rows.Count, ColumnNumber).End(xlUp).Offset(1)
      End If
  End With
End Function

---