S
stacyjean622
I copied a macro from Debra Dagliesh's contextures webpage and am trying to
modify it for my own project. (Basically I am trying to create an individual
worksheet for each student with multiple rows of that student's information
copied from a master worksheet.)
I keep running into a break at the "set rng" portion (see my note below). I
know I need to change "database" to read something else, but I have no idea
what. I can't see anything that was called "database" in the example I copied
the macro from so I can't figure out what my replacement word needs to be.
Here is the macro I am trying to use. I am woefully uninformed when it comes
to VBA, so please try to be as “dumbed down†as possible in responses. Thanks
for ANY help.
Sub ExtractStudents()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("ReportsGenerate")
Set rng = Range("Database") ****this is the most recent “break†in my
running of the macro***
'extract a list of Students
ws1.Columns("C:C").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row
'set up Criteria Area
Range("L1").Value = Range("C1").Value
For Each c In Range("J2:J" & r)
'add the student name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Thanks again, in advance for any help/advice
- s
modify it for my own project. (Basically I am trying to create an individual
worksheet for each student with multiple rows of that student's information
copied from a master worksheet.)
I keep running into a break at the "set rng" portion (see my note below). I
know I need to change "database" to read something else, but I have no idea
what. I can't see anything that was called "database" in the example I copied
the macro from so I can't figure out what my replacement word needs to be.
Here is the macro I am trying to use. I am woefully uninformed when it comes
to VBA, so please try to be as “dumbed down†as possible in responses. Thanks
for ANY help.
Sub ExtractStudents()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("ReportsGenerate")
Set rng = Range("Database") ****this is the most recent “break†in my
running of the macro***
'extract a list of Students
ws1.Columns("C:C").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row
'set up Criteria Area
Range("L1").Value = Range("C1").Value
For Each c In Range("J2:J" & r)
'add the student name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Thanks again, in advance for any help/advice
- s