Copy Names That Begin with A-L

Hello, I have googled the snot out of this and can't come up with an answer. I need a code that will search through B5:B40 on Sheet1, sort alphabetically, and then copy only the names that start with the letters A-L and past them into Sheet2 starting at B5. Then copy all the names from Sheet1 that start with M-Z and past them into Sheet3 starting at B5 again. Please help.

Hello there,

The following code should work for you:

Please Login or Register  to view this content.

To insert this code into your workbook
1. Press Alt+F8
2. Clear the macro name field and then type DistributeVal
3. Select the create option
4. In between the Sub DistributeVal() and End Sub copy and paste the above code. Anything that appears in green is a comment meant to help you understand.
5. Exit out of Visual Basic
6. Press Alt+F8
7. Select the DistributeVal macro
8. Select the Run option

Let me know if this works for you.

Thanks!

Another way based on there always being an entry for M

Please Login or Register  to view this content.

Hi Kyle!
I tried to use your code on Watersev's sheet, but I have got an error on this line

rng = Application.Transpose(Sheet1.Range("B4:B30").Value)

At a guess (I can't open watersev's document on my mac) he's not using an English Language version of Excel. Try changing:
Sheet1 to Sheets(1)
Sheet2 to Sheets(2)
Sheet3 to Sheets(3)

on mine watersev's worked

all i see is :

Please Login or Register  to view this content.

Thanks Mike

John, there are several ways of referencing sheets, my code will work fine on English versions of Excel, since Watersev doesn't use an English version of Excel his sheet object names (down the left hand side of the VBA IDE in the object Explorer) are not Sheet1 Sheet2 Sheet3 etc, rather they will be named in the language of the Excel installation that he is using so the objects that I reference do not exist.

You'll see that watersev uses an alternative style of referencing sheets Sheets(2), this uses the sheets collection to refer to a sheet and works irrespective of sheet name.

When I post code, I generally use the vba name of the sheet (as opposed to sheets("sheetname") or Sheets(1) which both refer to the sheets collection) since it will work after changing the sheet name property and remains constant regardless of sheet inserts and deletions, referring to the collection will not.

My suggestion above as to change the type of referencing in my code to that of the style used by watersev to remove the dependance on English Sheet names whilst using watersev's wb.

Does that clear it up any?

Yes, it does
so, u mean that he has sheets that aren't named"sheet1,sheet2,etc."