Splitting data into 5 sheets

[KLH2007]

Hey Everyone!!

I need to build a macro for excel to make my life easier. I've never done it and I am having a hard time figuring out how to get started. Could you point me in the right direction?

I get lists of customers every month and I need to break them into 5 additional sheets, Customers with birthdays listed, Customers with birthdays and emails listed, customers with emails listed, customers with sales date listed and customers with no sales date listed.

Thank you!!

[spitfireblue]

Hi KLH2007,

If you could please provide a sample file of the format of your data and also the format that you want it broken into we can help you.
Also, I suggest that you update your title into something like "Splitting data into 5 sheets" (please see forum rule #1). This will be helpful for someone one who is google searching for a similar problem.

[KLH2007]

Thank you so much Stephen! I have updated the title and I went to add my example but the attachment feature is not working for me. I am going to try tomorrow morning when i get to work.

[spitfireblue]

If you select "Go Advanced" then scroll down to Manage Attachments you can upload from there rather than using the paperclip (I believe there is some issues with this).

[KLH2007]

Thank you!! That is what I needed. You are so helpful!

[spitfireblue]

Try this:

Sub Split()
    Dim lrow As Long
    Dim cell As Range
    Dim ws As Long
    Dim C2 As Long, C3 As Long, C4 As Long, C5 As Long, C6 As Long
    
    For ws = 2 To Sheets.Count
        With Sheets(ws)
            .Range("2:100").ClearContents
        End With
    Next
    
    C2 = 2
    C3 = 2
    C4 = 2
    C5 = 2
    C6 = 2
        
    Sheets(1).Activate
    lrow = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each cell In Range("A2:A" & lrow)
        If Not cell.Offset(0, 8).Value = "" Then
            cell.EntireRow.Copy Destination:=Sheets(2).Range(C2 & ":" & C2)
            C2 = C2 + 1
        End If
        If Not cell.Offset(0, 9).Value = "" Then
            cell.EntireRow.Copy Destination:=Sheets(3).Range(C3 & ":" & C3)
            C3 = C3 + 1
        End If
        If Not cell.Offset(0, 2).Value = "" Then
            cell.EntireRow.Copy Destination:=Sheets(4).Range(C4 & ":" & C4)
            C4 = C4 + 1
        End If
        If Not cell.Offset(0, 2).Value = "" And Not cell.Offset(0, 3).Value = "" Then
            cell.EntireRow.Copy Destination:=Sheets(5).Range(C5 & ":" & C5)
            C5 = C5 + 1
        End If
        If Not cell.Offset(0, 3).Value = "" Then
            cell.EntireRow.Copy Destination:=Sheets(6).Range(C6 & ":" & C6)
            C6 = C6 + 1
        End If
    Next

End Sub

[KLH2007]

Thank you!!

I am getting an error "run time error '9' subscript out of range"

When I click Debug it highlights:

cell.EntireRow.Copy Destination:=Sheets(4).Range(C4 & ":" & C4)


What can I change to remove that error?

[spitfireblue]

Hmm, I can't pick what the error is... here is the sheet that I used that isn't coming up with and error for me.