Use macro to check a range of cells within a row and copy wanted data to new sheet
 

This worked perfectly!!!!!

Thank you so much!

You have just saved me tons of work. Awesome. I promise to study th
code so that instead of asking questions in the future, I can hopefull
contribute.


Bernie Deitrick Wrote:
bus,

MACRO

You can use a macro - the macro below will extract all the data for a
particular class code and copy it to a new worksheet.

MANUAL

The manual method that depends on one additional column of formulas
Firts,
though, you will need a single cell for the class code that you wan
to
extract. Let's use A1 for our example.

Then use a column of formulas - in, let's say, column I, which I'l
assume
to be blank: Enter this if cell I2:

=NOT(ISERROR(MATCH($A$1,A2:H2,FALSE)))

and copy that down to match your table.

Then use Data | Filter... Autofilter, and select the TRUE values o
column
I. Then select your entire data table, use Edit | Go To... Special
Visible
cells, then do your copy and paste to the new sheet.

HTH,
Bernie
MS Excel MVP

Sub FindClassCodeValues()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFindString As String
Dim firstAddress As String
Dim mySht As Worksheet

myFindString = InputBox("Enter the class code", , "Hello")
With Cells
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
'Then all the rows of the cells that have been found

Set mySht = Worksheets.Add
mySht.Name = "Class " & myFindString
d.EntireRow.Copy mySht.Range("A1")

End Sub



"busspeed" wrote in message
...

I have a problem and am not very familiar with VBA.
What I need to do is check a row of data one at a time (there ar
about
3000 total rows) looking for a class number (the class numbers ar
in
different columns within the row). When I find that class numbe
within
the row I need to *copy* the row to another sheet. I am simply tryin
to
grab all of the students in certain classes and paste them o
another
sheet.

Here is a sample of the data:
Adams gadams21 041184 84022 84260
Adams hadams4 111591 83772
Adams dadams25 041645 84020 84282
Adams madams3 082981 83969
Adams sadams9 120369 81838 84025 86460

The first column is last name (yes, I have changed the data...non
of
it is sensitive).
The second column is user name.
3rd column is birthday.
Remaining columns are classes they are in (can vary; some can be i
5
classes, some in 1).

Any help would be greatly appreciated.

Thanks,

busspeed


--
busspeed

------------------------------------------------------------------------
busspeed's Profile:
http://www.excelforum.com/member.php...o&userid=14829
View this thread
http://www.excelforum.com/showthread...hreadid=264630


--
busspee
-----------------------------------------------------------------------
busspeed's Profile: http://www.excelforum.com/member.php...fo&userid=1482
View this thread: http://www.excelforum.com/showthread.php?threadid=26463