Copying Multiple Cells on one sheet to Another Sheet w/ Varying Situations

[USFengBULLS]

Hello All,

I have made two previous codes that copy cells from one sheet that is a form and paste them in the next blank cell on another sheet that is a Log sheet.
They looked something like this:

If COStatus = "INITIATED" Then
InitiatedDate = ActiveSheet.Range("F3")
'Copies Values from CO FORM to CO LOG
erw = Sheet3.Range("A" & Rows.Count).End(xlUp).Row + 1
Sheet3.Cells(erw, 1) = Range("B7")
Sheet3.Cells(erw, 3) = Range("B9")
Sheet3.Cells(erw, 4) = Range("D8")
Sheet3.Cells(erw, 5) = Range("D3")
Sheet3.Cells(erw, 6) = Range("D6")
Sheet3.Cells(erw, 8) = InitiatedDate
Sheet3.Cells(erw, 13) = Range("F6")

This time I am creating something a little different from this. In the code above its just take this one cell and puts it here in this column. But this time I need it to take a range of cells and put them in this column, but not all cases will all 5 rows be filled.

For instance, on the form sheet
Cells E23:27 = Description of Sample
Cells B23:27 = MR Code
Cells D23:27 = AC Code
If one row (meaning B23, D23 and E23) are filled or if 3 rows meaning(B23:25, D23:25 and E23:25) are filled or if all 5 rows (meaning B23:27, D23:27 and E23:27) are filled...whatever the case may be I need it to copy all of the B23:27 items to the Log sheet in column C, all of the D23:27 cells to the log sheet in Column D and all of the E23:27 cells to the log sheet in Column B.
Also, I need to copy the Transmittal number which is static for each of these lines, so if all 5 rows are being used in this case, the transmittal number is still 1 for this, or 2 for the next that only has 3 rows in use, and so on. This Transmittal number is in Range ("G7") on the form sheet will be the active sheet as I start this code because I will tie this code into an ActiveX Private Sub command Button. This Transmittal number will have to go each time to Column A on the Log sheet with each item I throw over there from the B,D,E cells.

Last but not least, like the transmittal number I need to carry the date over as well which is static to each transmittal number. So for transmittal 1 the date is 1/15/2019 and there are 5 rows that need to be logged then in Column A transmittal number will be 1 for each and the date (which is Range"G6" on the form sheet) will need to go with each row in column F
Basically, Range G6 and G7 are the same each time but we don't know how many rows of the B,D, E23:27 will be filled out each transmittal. However many that is, it needs to be copied over to the log with the transmittal number and date, along with the description, MR Code, and AC code in the correct columns on the log sheet as stated above.
(Remember, I still have to find the next blank row in the log too at the start of this transfer. Something like the erw in my code I posted and used for my previous applications.)

Anyone can get me started with this that would be such a huge help. Haven't wrote a code for something like this so its definitely new to me. I hope I wasn't too confusing in explaining this.

 

[DanteAmor]

Annex the code

    If COStatus = "INITIATED" Then
        If Range("G7").Value = "" Then
            MsgBox "Please fill Transmittal number"
            Exit Sub
        End If
        '
        For i = 23 To 27
            If Cells(i, "B").Value = "" Then
                Exit For
            End If
            'Copies Values from CO FORM to CO LOG
            erw = Sheet3.Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheet3.Cells(erw, "A") = Range("G7")    'trans number
            Sheet3.Cells(erw, "B") = Range("E" & i) 'Description of Sample
            Sheet3.Cells(erw, "C") = Range("B" & i) 'MR Code
            Sheet3.Cells(erw, "D") = Range("D" & i) 'AC Code
            Sheet3.Cells(erw, "F") = Range("G6")    'date
        Next
    End If

Try and tell me

 

[USFengBULLS]

Thanks, that worked perfectly!

 

[DanteAmor]

I'm glad to help you