Copying Rows based on Cell Values and then adding subtotals

tehtruth89

New Member
Joined
Sep 5, 2014
Messages
1
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 ---
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,561
Messages
5,832,512
Members
430,138
Latest member
silver_john

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
Top