Transfer data from one sheet to another based on cell value not being empty

Hi there,

I wrote this vba code that should transfer the data from one worksheet to another. I need it to transfer the adjacent data if the value on a cell is not empty (from the "Form" worksheet) on the last row of the "Email" worksheet. However, I don't know why VBA is only honoring the last If statement and completely ignoring (or maybe over writing the first to If statements. SO I basically need to have the "Email" worksheet to have a total of 3 entries but instead, I'm only getting one (the last if statement). Any help would be appreciated.

Here's my code:

Private Sub cmdEmail_Click()

Dim lRow As Long
Dim sht As Worksheet
Set sht = Sheets("Email")
lRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


If Not IsEmpty(Sheets("Form").Range("K23").Value) Then
    Range("A" & lRow + 1).Value = Sheets("Form").Range("K23").Value
    Range("B" & lRow + 1).Value = Sheets("Form").Range("E23").Value
    Range("C" & lRow + 1).Value = Sheets("Form").Range("F23").Value
    Range("D" & lRow + 1).Value = Sheets("Form").Range("H23").Value
End If

If Not IsEmpty(Sheets("Form").Range("K27").Value) Then
    Range("A" & lRow + 1).Value = Sheets("Form").Range("K27").Value
    Range("B" & lRow + 1).Value = Sheets("Form").Range("E27").Value
    Range("C" & lRow + 1).Value = Sheets("Form").Range("F27").Value
    Range("D" & lRow + 1).Value = Sheets("Form").Range("H27").Value
End If

If Not IsEmpty(Sheets("Form").Range("K37").Value) Then
    Range("A" & lRow + 1).Value = Sheets("Form").Range("K37").Value
    Range("B" & lRow + 1).Value = Sheets("Form").Range("E37").Value
    Range("C" & lRow + 1).Value = Sheets("Form").Range("F37").Value
    Range("D" & lRow + 1).Value = Sheets("Form").Range("H37").Value
End If



End Sub

It looks like you're writing to the same cells in each of the 3 IF statements, so your last IF statement is overwriting the first two. Not sure if this will give you exactly what you're looking for, but try changing the +1 in the second IF statement to +2 and to +3 for the third if statement.

Originally Posted by mo4391

It looks like you're writing to the same cells in each of the 3 IF statements, so your last IF statement is overwriting the first two. Not sure if this will give you exactly what you're looking for, but try changing the +1 in the second IF statement to +2 and to +3 for the third if statement.

Hi mo4391,

Thank you for your reply. That will actually work out if the mentioned cells are always not empty. However, there are instance that one or more will be empty. Your idea of changing the +1 will insert empty rows on the "Email" spreadsheet. I need it to be inserted on the last row. Any other idea?

Thank you.

That worked perfectly! Thank yo mo4391! Cheers!

Originally Posted by mo4391

Then it sounds like you need to re-define your lRow variable if either of the first two IF statements are true. There are probably other people smarter than me on this message board that would have a more elegant solution, but see if this works for you:

Private Sub cmdEmail_Click()

Dim lRow As Long
Dim sht As Worksheet
Set sht = Sheets("Email")
lRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


If Not IsEmpty(Sheets("Form").Range("K23").Value) Then
    Range("A" & lRow + 1).Value = Sheets("Form").Range("K23").Value
    Range("B" & lRow + 1).Value = Sheets("Form").Range("E23").Value
    Range("C" & lRow + 1).Value = Sheets("Form").Range("F23").Value
    Range("D" & lRow + 1).Value = Sheets("Form").Range("H23").Value
    lRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
End If

If Not IsEmpty(Sheets("Form").Range("K27").Value) Then
    Range("A" & lRow + 1).Value = Sheets("Form").Range("K27").Value
    Range("B" & lRow + 1).Value = Sheets("Form").Range("E27").Value
    Range("C" & lRow + 1).Value = Sheets("Form").Range("F27").Value
    Range("D" & lRow + 1).Value = Sheets("Form").Range("H27").Value
    lRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
End If

If Not IsEmpty(Sheets("Form").Range("K37").Value) Then
    Range("A" & lRow + 1).Value = Sheets("Form").Range("K37").Value
    Range("B" & lRow + 1).Value = Sheets("Form").Range("E37").Value
    Range("C" & lRow + 1).Value = Sheets("Form").Range("F37").Value
    Range("D" & lRow + 1).Value = Sheets("Form").Range("H37").Value
End If



End Sub