Copy Data from A Sorted List On One Sheet To A Specific Area On Another Sheet

[be_be]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m just a novice when it comes to VBA coding but I can follow directions when it comes to some vba coding.<o></o>
Setup:<o></o>
I have a student name range setup (“STUDENTS”) on SHEET2 where the names go from A to Z. The columns across are labeled “Last”, “First”, “Address”, and “Year Graduated”. Currently, there are about 500 students on this list.
Problem:<o></o>
I want to <ENTER>a last name in the “<ENTER> Last Name” box (“LastB” at O102) and have all the students with the same last name extracted starting at T103 along with their address and graduating CLASS on SHEET1.
<o></o>
Also, in the NAME column, each name will be reformatted to show “FIRST” AND “LAST” name format (expl. Linda Allen instead of Allen Linda).<o></o>
<o></o>
I have other sheets calling for similar information but if I can see this one through, I could figure the others out.<o></o>
Thank You in advance.<o></o>

 

[Snakehips]

be-be,

Give this a try.......
You can tweak it some if it is not exactly what you want.

Sub GetLastB()
'set to named range
Set StudRange = Sheets("Sheet2").Range("STUDENTS")
'get rows in named range
MaxStuds = StudRange.Rows.Count
'Get surname to find
LastB = Sheets("Sheet1").Range("O102").Value
'set start cell for list
Set StartNew = Sheets("Sheet1").Range("T103")
'Clear old list 1000 rows down 4 cells accross
StartNew.Resize(1000, 4).ClearContents
'set counter for same names
SameName = 0
'Look for names
For s = 1 To MaxStuds
'Skip to next row if not a match
If Not StudRange.Cells(s, 1).Value = LastB Then GoTo NextStud
'Quit if surname blank  assumes blank = end of list!!!
 If Not StudRange.Cells(s, 1).Value = LastB Then GoTo Out
    'do if name matches
    'Flip and concatenate name and put data to sheet 1
        FlipName = StudRange.Cells(s, 2).Value & " " & StudRange.Cells(s, 1).Value
        StartNew.Offset(SameName, 0).Value = FlipName
        StartNew.Offset(SameName, 1).Value = StudRange.Cells(s, 3).Value
        StartNew.Offset(SameName, 2).Value = StudRange.Cells(s, 4).Value
        SameName = SameName + 1
NextStud:
Next s
Out:
'Autofit the columns
Sheets("Sheet1").Columns.AutoFit
End Sub

You have a choice as to how you call / run the macro.

Let me know if that helps.

 

[be_be]

Oops! There was a run-time error "9": Subscript out of Range
on the first line of code.
Set StudRange = Sheets("Sheet13").Range("S_Base")
(I tested the code on another set of students with the same parameters on a different sheet with the list called "S_Base").
Went back and tested it on the initial list (Students) and it still gave an error.

 

[Snakehips]

be_be,
Code should be fine.

Are you using the correct sheet names??
It is possible that you are using Excels fixed sheet reference rather than your given sheet name.
When using the code reference - Sheets("Bla Bla") - Bla Bla must be the EXACT name on the sheet tab including the exact number of spaces, should there be any.

Keep me posted.

 

[be_be]

Hi Snakehips
Went back and edit the Sheet Name and it Worked. Thanks again for your help.