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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,214,659
Messages
6,120,784
Members
448,992
Latest member
prabhuk279

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