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

[gwacouk]

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.

[SamT]

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

[gwacouk]

Thank you

[SamT]

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


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



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


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