alphabetic list - copy each letter to different worksheet

**Armitage2k** · 11-20-2009, 06:44 AM

Hi!

I have a list with approx. 60.000 entries (one per row) and one empty row after each letter. Eg:

Adam
Albert
Aston
<empty row>
Barbara
Barbie
Busty
<empty row>
Charles
...

Now, what I would need to do is to cut the rows of all entries which start with the same letter and paste them into a worksheet named after the respective letter (e.g "A", "B", "C", etc.).
Well, since this work is a little tiring, I was hoping that someone could help me with a macro who does that simple work for me, please

Every help is highly appreciated!
Thanks a lot,
A2k

**JeanRage** · 11-20-2009, 07:00 AM

Hi,

Have you tried, with a small sample, to turn on your macro recorder to start building your own macro ...?

HTH

**Andy Pope** · 11-20-2009, 07:10 AM

Assume list of names is in column A of active sheet. A1 needs to contain a field header.

A1: =Names
A2: =Adam
A3: =Albert
etc

Sub x()

    Dim rngData As Range
    Dim objSht As Object
    Dim lngIndex As Long
    Dim rngCopy As Range
    
    Set rngData = ActiveSheet.Range("A1", ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp))
    For lngIndex = 1 To 26
        rngData.AutoFilter 1, Chr(64 + lngIndex) & "*"
        Set rngCopy = rngData.SpecialCells(xlCellTypeVisible)
        If rngCopy.Cells.Count > 1 Then
            Set objSht = Worksheets.Add(after:=Worksheets(Worksheets.Count))
            objSht.Name = Chr(64 + lngIndex)
            rngCopy.Copy objSht.Range("A1")
        End If
        rngData.AutoFilter
    Next
End Sub

**Armitage2k** · 11-20-2009, 08:16 AM

Thanks for the help andy!

However, this is almost what I need. Instead of just copying the first cell (A1), could you make it copy the whole row?
I tried to adjust the code but failed miserably

Thanks!
A2k

**Andy Pope** · 11-20-2009, 09:50 AM

just change the copy method to include the entirerow

            rngCopy.EntireRow.Copy objSht.Range("A1")

**winlogo** · 07-17-2010, 06:05 PM

Hello,

How this macro can works with number?
If the table
A1. 12
A2. 56
A3. 67

Max id 100 and make new sheet with id number

Thanks

**shg** · 07-17-2010, 08:17 PM

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.

alphabetic list - copy each letter to different worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

alphabetic list - copy each letter to different worksheet

Re: alphabetic list - copy each letter to different worksheet

Re: alphabetic list - copy each letter to different worksheet

Re: alphabetic list - copy each letter to different worksheet

Re: alphabetic list - copy each letter to different worksheet

Re: alphabetic list - copy each letter to different worksheet

Re: alphabetic list - copy each letter to different worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions