I understand that - but I still don't know what I need to change it to.
"Database" certainly doesn't seem to work for me, but I know this range needs
to refer to more than just my "reportsgenerate" worksheet. (At least I think
it does) Later in the code, rng seems to refer to the new worksheets the code
creates.

If I use a range like A1:M:150 it will ALMOST run my code, but it gets stuck
near the end when it is setting up the new worksheets:

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A10"), _
Unique:=False

I've tried naming the range ThisWorkbook; the title of my workbook; etc. I
know I'm doing something really wrong, but I'm not familiar enough with
programming to know what.

- s

"JLGWhiz" wrote:

What she did there was to set the variable rng to a range named "Database".
It could just as well have been: Set rng = Sheets(1).Range("A1:A5")
Technically it sets an object variable so that the variable "rng" can be
used in the code instead of having to spell out the full object name each
time.

"stacyjean622" wrote:

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