PDA

View Full Version : Macro to pull Duplicate ID # and Absent



Pam in TX
10-25-2010, 08:59 AM
I need assistance with the macro in the attached workbook...


Here is what it should be doing after prompting for the csv file and creating a workbook:

Create 2 new sheets and copy the first 2 rows of sheet one to each of them
Look for duplicate Student Id numbers and place both on the DUPLICATE ID tab
Look for students who have no answers at all and place them on the ABSENT tabSince I can only post one file I will make a second post for the data file...

Thank you in advance for you help....

Pam in TX
10-25-2010, 09:01 AM
Here is the data file:

mbarron
10-25-2010, 09:36 AM
Try this one (the "master sheet" must be the active sheet when run):

Sub dupAndAbs()

Dim sOrig As Worksheet, sDup As Worksheet, sAbs As Worksheet
Dim i As Long
Application.ScreenUpdating = False

i = 3
Set sOrig = ActiveSheet
Set sDup = Sheets.Add(after:=sOrig)
sDup.Name = "Duplicate ID"
Set sAbs = Sheets.Add(after:=sDup)
sAbs.Name = "Absent"

sOrig.Range("a1:a2").EntireRow.Copy
sDup.Range("a1").PasteSpecial
sDup.Activate
sDup.Range("a1").Select
sAbs.Range("a1").PasteSpecial
sAbs.Activate
sAbs.Range("a1").Select

With sOrig
Do Until .Cells(i, 1) = ""
If Application.WorksheetFunction.CountIf(.Range("i:i"), .Cells(i, "I")) > 1 Then
.Cells(i, 1).EntireRow.Copy _
sDup.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
i = i + 1
Loop
End With
sDup.Cells.EntireColumn.AutoFit

With sDup
Range(.Cells(3, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Sort key1:=.Range("I3"), order1:=xlAscending, Header:=xlNo
End With


i = 3
With sOrig
Do Until .Cells(i, 1) = ""
If Trim(.Cells(i, "j")) = "" Then
.Cells(i, 1).EntireRow.Copy _
sAbs.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
i = i + 1
Loop
End With
sAbs.Cells.EntireColumn.AutoFit

sOrig.Select
sOrig.Range("a1").Select

Application.ScreenUpdating = True

End Sub

Pam in TX
10-25-2010, 10:17 AM
That didn't work..... Thanks though....

It pulled students with answers to the "absent" tab.....

I need to be able to get the prompt for the csv file and then for the macro to pull off the data for the diferent tabs.... The macro needs to ignore anything in the "Absent" column....

This macro was working until we reorganized the columns due to a software update which was out of our control.....