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....
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.....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.