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

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


This is great:) Is it possible that the first line of each sheet will not be cleared?

Thank you!
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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.