Copying Rows based on Cell Values and then adding subtotals

[tehtruth89]

Hello,

I am fairly new to vba. Before I decided to post this question, I did some research and found a similar solution, but was unable to successfully implement the code http://www.mrexcel.com/forum/excel-...entire-row-second-sheet-based-cell-value.html

I am on Microsoft Excel 2010 and windows 7. Basically, I am using an outside software to generate raw data into a blank workbook sheet called "Data." I would like to copy the data from sheet "Data" and present it in a sheet called "Summary" with the following format (I would like to add subtotals):


-- removed inline image ---


Below is what the raw data dump would look like. Using SQL server, I am bringing in column A as either "1" or "2." The rows that have value "1" would be placed starting on row 6 of sheet "Summary." The rows that have value "2" would start two rows after the first data set. I would also like to exclude column A from the "Summary" Sheet The following is some of Michael's code that I tried modifying,but was unsuccessful. I understand that the code I inserted below is wrong, which is why I am posting this question:

Sub CopyData()

Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Range("A" & r).Value = "1" Then
Rows(r).Copy Destination:=Sheets("Summary").Range("A" & lr2 + 1)
lr2 = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("A" & r).Value = "2" Then
Rows(r).Copy Destination:=Sheets("Sheet1").Range("A" & lr2 + 1)
lr3 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub



-- removed inline image ---