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

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


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

Thank you!
0
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.
0
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
0
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.
0