Setting Range in VBA - partial repost

[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

 

[JLGWhiz]

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]

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

 

[michael.beckinsale]

Hi Stacey,

Without going to Deborah's website it would seem logical that the
'Database' is the range where you have your students listed.

So if the names are listed in column A rows 1 to 20 you would replace
"Database" with "A1:A20"

Regards

Michael

 

[stacyjean622]

Michael - it worked! Thanks so much!

Stacy

 

[michael.beckinsale]

Hi Stacey,

Seems my post & JLGWhiz crossed.

I have just had a quick look at Deborah's example file.

From your recent post it appears your details (or "database") are in
the range "A1:M150" on the sheet "ReportsGenerate". If this is correct
there are a number of things you need to change:

1) Replace:
Set rng = Range("Database")
With:
Set rng = Sheets("ReportGenerate").Range("A1:M150")

2) Deborah uses columns J & L to set up temporary data but your
"database" already extends to column M. Therefore throughtout the code
replace column J with O and L with Q.

Also note that Deborah's names are in column C, if your student names
are in a different column that needs changing as well.

The above should get you up & running but please be aware that
although technically there is no limit to the number of sheets Excel
can handle there are many dependencies. I have had workbooks where the
number of sheets l could create using VBA is much less than 150 (or
however many unique student names you have.)

Hope this helps

Regards

Michael