Question

1 Create a macro to extract all of the data from the year 2015 and copy it into a separate block. 2 create a table that displays the minimum concentration for each month in the copyed data. 3 Add a button to run the macro. 5 Date Concentration (mcg/I) 6 2/21/2015 79/2/2015 8 1/17/2015 9 4/30/2015 01/1/2016 11 10/31/2015 12 8/26/2016 13 12/6/2016 14 5/19/2015 15 10/21/2016 16 6/22/2016 17 1/13/2016 18 4/4/2015 0.824 5.099 0.427 1.594 5.278 9.616 7.474 5.696 1.606 4.598 8.663 6.943 0.87 8.657 8.65 2.725 1.005 19 11/6/2015 20 8/12/2016 21 7/6/2015 22 3/17/2015

the macro must be done through VBA (visual basic aid) on excel. thank you

Answer 1

Create a button by clicking on developer tab and add controls and a button.

In my case I have named it as extract.

Below is the macro code which will get invoked when the button is clicked

Sub Button1_Click()

Dim sourceRow, destRow

destRow = 5

ThisWorkbook.Sheets("Sheet1").Activate

sourceLR = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For sourceRow = 5 To sourceLR ' Initiate from Row 5 since we have data from Row 5

    If (Sheet1.Cells(sourceRow, 1).Value < DateValue("December 31,2015")) Then

        Range(Cells(sourceRow, 1), Cells(sourceRow, 2)).Copy Range(Cells(destRow, 7), Cells(destRow, 8))

        destRow = destRow + 1

    End If

Next sourceRow

   

End Sub

Screen clipping taken: 26-02-2019 08:24

I have not included answer to the question no. 2 .