Help! Trying to copy rows of data via macro
Closed
Cman
-
Sep 8, 2009 at 11:20 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 16, 2011 at 08:01 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 16, 2011 at 08:01 PM
Hello,
I could really use some help. Here is what I am trying to do.I have a mound of data that i want to break up into different sheets. I want to use a Macro to go through and separate based on the different values of a certain column. Is there anyway to do this? For instance, Let's say I have Salesman and the units they sold. Let's say for this example I have 1 unit, 2 units, and 3 units sold all the way down. So i want a macro to break up all the salesmen with 1 unit sold in one sheet. All the salesmen with 2 units sold in a second sheet. And all of the salesmen with 3 units sold in a third sheet. (my real life data is 500,000 records... so manual will take forever!) Any help would be GREATLY appreciated.
Thank you!!!!
Cman
I could really use some help. Here is what I am trying to do.I have a mound of data that i want to break up into different sheets. I want to use a Macro to go through and separate based on the different values of a certain column. Is there anyway to do this? For instance, Let's say I have Salesman and the units they sold. Let's say for this example I have 1 unit, 2 units, and 3 units sold all the way down. So i want a macro to break up all the salesmen with 1 unit sold in one sheet. All the salesmen with 2 units sold in a second sheet. And all of the salesmen with 3 units sold in a third sheet. (my real life data is 500,000 records... so manual will take forever!) Any help would be GREATLY appreciated.
Thank you!!!!
Cman
Related:
- Help! Trying to copy rows of data via macro
- Tmobile data check - Guide
- Gta 5 data download for pc - Download - Action and adventure
- Digital data transmission - Guide
- Data transmission cable - Guide
- Transfer data from one excel worksheet to another automatically - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 9, 2009 at 09:51 PM
Sep 9, 2009 at 09:51 PM
try this macro. there is a FUNCTION . that is required. . both macro and Function may be copied in vb editor in a module.
Sub test() Dim rng As Range, rng1 As Range, rng2 As Range, c2 As Range Dim x As Integer, cfind As Range, add As String, dest As Range With Worksheets("sheet1") Set rng = Range(.Range("c1"), .Range("c1").End(xlDown)) Set rng1 = .Range("a1").End(xlDown).Offset(5, 0) rng.AdvancedFilter action:=xlFilterCopy, copytorange:=rng1, unique:=True Set rng2 = Range(rng1.Offset(1, 0), rng1.End(xlDown)) For Each c2 In rng2 x = c2.Value If Not SheetExists("class" & x) Then Worksheets.add ActiveSheet.Name = "class" & x ActiveSheet.Move after:=Sheets(Sheets.Count) Else Worksheets("class" & x).Cells.Clear GoTo line2 End If line2: Set cfind = rng.Cells.Find(what:=x, lookat:=xlWhole) If cfind Is Nothing Then GoTo line1 add = cfind.Address Range(.Cells(cfind.Row, 1), .Cells(cfind.Row, cfind.Column)).Copy With Worksheets("class" & x) Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0) dest.PasteSpecial End With Do Set cfind = rng.Cells.FindNext(cfind) If cfind Is Nothing Then GoTo line1 If cfind.Address = add Then GoTo line1 Range(.Cells(cfind.Row, 1), .Cells(cfind.Row, cfind.Column)).Copy With Worksheets("class" & x) Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0) dest.PasteSpecial End With Loop line1: Next c2 End With End Sub Function SheetExists(SheetName As String) As Boolean ' returns TRUE if the sheet exists in the active workbook SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets(SheetName).Name) > 0 Then SheetExists = True Exit Function End If NoSuchSheet: End Function
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 8, 2009 at 09:41 PM
Sep 8, 2009 at 09:41 PM
I suppose it is possible. please attach small extract of your fiel.
Here is a sample:
Product Store Class
X111 1 1
Y542 555 1
G234 34 2
A567 678 2
B112 23 3
C009 567 3
P010 456 4
I am trying to run a macro that will separate by Class into separate sheets. I appreciate any help I can get!!!
Thanks!!!
Cman
Product Store Class
X111 1 1
Y542 555 1
G234 34 2
A567 678 2
B112 23 3
C009 567 3
P010 456 4
I am trying to run a macro that will separate by Class into separate sheets. I appreciate any help I can get!!!
Thanks!!!
Cman
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 16, 2011 at 08:01 PM
Jan 16, 2011 at 08:01 PM
1. this question is attached to a very od 2009 thread. open a new thread and then explain your problem.
2. yes it can be done. you can filter the data.
post a very small extract of your data in the NEW THREAD.
2. yes it can be done. you can filter the data.
post a very small extract of your data in the NEW THREAD.
Jan 15, 2011 at 09:16 PM
Thank you!