Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Range in VBA - partial repost
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Range in VBA - partial repost
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Range in VBA - partial repost
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Range in VBA - partial repost
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Range in VBA - partial repost
Michael - it worked! Thanks so much!
Stacy "michael.beckinsale" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Range in VBA - partial repost
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range REPOST | Excel Worksheet Functions | |||
Repost: Copy Range from one wbk to another | Excel Programming | |||
Partial Annual Periods (Repost) | Excel Worksheet Functions | |||
Repost: Removing Cells from a Range | Excel Programming | |||
Repost: Any way to do this with a range? | Excel Programming |