VBA - Use macro to move data from multiple column ranges in one sheet to another

[Hopeful]

Hello,
VBA newbie here.

I am a greenhouse manager attempting to utilise an excel workbook to assist in my chemical compliance.
Sheet 1 is a "work order" page which has a layout involving a number of merged cells in order to provide a visually pleasing work order which can be printed and provided to the spraying contractor(s). Some of these cells rely on drop down boxes and vlookups.

I wish to fill out sheet 1 weekly and then run a macro which is assigned to a Command Box within the sheet which copies the data from selected ranges (only if they contain values) to Sheet 3, then clears the cells which have drop down boxes (thus clearing the vlookup cells). Sheet 3 will be my database to store the history of sprays applied to crops throughout the season(s).

At the moment I have got so far as successfully moving one row (R14) across the particular columns I am interested in from Sheet 1 to Sheet 3 and then clearing the cells that contain drop down boxes. However I don't know how to get the same to occur from row 15 down to row 29.

Please lend a hand - I look forward to getting some help from the brains trust! ?

An example of the code thus far:

Private Sub CommandButton1_Click()
erw =Sheet3.Cells(1,1).CurrentRegion.Rows.Count+1
If Len(Range("B14")) <> 0 Then
Sheet3.Cells(erw,1)= Range("B14")
Sheet3.Cells(erw,2)=Range("C14")
Sheet3.Cells(erw,3)=Range("D14")
Sheet3.Cells(erw,4)=Range("E14")
Sheet3.Cells(erw,5)=Range("G14")
Sheet3.Cells(erw,6)=Range("J14")
Sheet3.Cells(erw,7)=Range("M14")
Sheet3.Cells(erw,8)=Range("P14")
Sheet3.Cells(erw,9)=Range("R14")

Range ("B14")=""
Range ("C14")=""
Range ("D14")=""
Range ("E14")=""
Range ("G14")=""
Range ("J14")=""

Else
MsgBox "You must select a location to apply chemical"

End If

 

[JLGWhiz]

Try this modified version

Private Sub CommandButton1_Click()
Dim i As Long
 For i = 14 To 29
    erw = Sheet3.Cells(1, 1).CurrentRegion.Rows.Count + 1
    If Len(Range("B" & i)) <> 0 Then
        Sheet3.Cells(erw, 1) = Range("B" & i)
         Sheet3.Cells(erw, 2) = Range("C" & i)
         Sheet3.Cells(erw, 3) = Range("D" & i)
         Sheet3.Cells(erw, 4) = Range("E" & i)
         Sheet3.Cells(erw, 5) = Range("G" & i)
         Sheet3.Cells(erw, 6) = Range("J" & i)
         Sheet3.Cells(erw, 7) = Range("M" & i)
         Sheet3.Cells(erw, 8) = Range("P" & i)
         Sheet3.Cells(erw, 9) = Range("R" & i)
         Range("B" & i) = ""
         Range("C" & i) = ""
         Range("D" & i) = ""
         Range("E" & i) = ""
         Range("G" & i) = ""
         Range("J" & i) = ""
    Else
        MsgBox "You must select a location to apply chemical"
    End If
  Next
End Sub

Also, When posting code, use the # symbol in the tool bar to put the code tags on and retain the formatting. Just select the code with the mouse pointer and then click the # symbol. It will put the code tags on for you.

 

[Hopeful]

Thank you JLGWhiz, you have honestly saved me from a late night (or more!) of Googling/YouTubing.

I got some weird loop occuring with the MsgBox statement so I removed it and just left Else there.

Thanks again!

 

[JLGWhiz]

Thank you JLGWhiz, you have honestly saved me from a late night (or more!) of Googling/YouTubing.

I got some weird loop occuring with the MsgBox statement so I removed it and just left Else there.

Thanks again!

Yep, I can see that it would run the gamut If nothing was entered in column B on the next available line between 14 and 29. You could use the message box if you put a line after it like

Else
        MsgBox "You must select a location to apply chemical"
        Exit Sub