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

Hopeful

New Member
Joined
Apr 6, 2018
Messages
2
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try this modified version
Code:
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
Code:
Else
        MsgBox "You must select a location to apply chemical"
        Exit Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top