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
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