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

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,071
Members
414,281
Latest member
Engjamal2021

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