Dynamically create named range? 2

[LTillner]

Is there a way to dynamically create named ranges?

I have a worksheet created from a query that I can filter
and subtotal such as:

    Workbooks.Open Filename:=vTmpASO
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=vGLevel
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

what I then want to do is create named ranges for each subgroup (in this case the Test that was taken). The reason I need the named ranges is to copy the data into a defined template location in another worksheet for the final layout.

I would also like to turn this code into a loop so that it will automatically go through and re-filter and re-subtotal for each gradelevel (3-10) and I would end up with named ranges for each test within each grade level.

Thanks for your help!
Lynette

 

[PBAPaul]

Lynette

Try this

Sub NameTheRange(NameForRange As String)
    Dim SheetName As String
    SheetName = ActiveSheet.Name
    SheetName = "='" & SheetName & "'!" & Selection.Address
    ActiveWorkbook.Names.Add Name:=NameForRange, RefersTo:=SheetName
End Sub

Sub TestTheCode
    Sheets("Sheet1").select
    Range("A1:B10").select
    NameTheRange "MyTestRange"
End Sub

A range Name is made up of the Sheet Name and the range address. The routine TestTheCode will use the macro "NameTheRange" to create a rangename of "MyTestRange") for 'Sheet1'!A1:B10.

Paul

 

[AnotherHiggins]

Have a look at the OFFSET function in Excel. There is a FAQ on creating dynamic ranges here: faq68-1331.

An example:
the arguments for the function are:
=offset(ReferenceCell - acts as an anchor,how many rows from the reference will the top left corner of the range be,how many columns from the reference will the top left corner of the range be,How many cells tall is the range,how many cells wide is the range)

Let's consider this data in a worksheet:
[tt]
A B C D
1 Header1 Header2 Header3 Header
2 1 3 5 7
3 2 1 4 8
4 3 2 7 0
5 8
[/tt]

Naming column B dynamically to be as 'tall' as the populated area would look like this:
=offset($A$1,1,1,count($B:$B),1)

To account for non-numerical data, change count to counta, but make sure to account for the header:
=offset($A$1,1,1,counta($B:$B)-1,1)

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[LTillner]

OK, Thanks, however, in playing with what I need to do, I don't need to go to the extra step of naming the ranges at all.

I just need to filter and then get the filtered range to the right place in the template.

This is my recorded macro code now:

    Range("B2").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="3 ELA ApS T3"
    Range("C2:I3").Select
    Selection.Copy
    Windows("Fall.xls").Activate
    Sheets("f1STUR_Tmplt").Select
    Range("H5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ASOdata.xls").Activate
    Selection.AutoFilter Field:=2, Criteria1:="3 MA ApS T6"
    Range("C4:I11").Select
    Selection.Copy
    Windows("Fall.xls").Activate
    Range("H25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

This is two passes through. Notice the Criteria1
in the first pass it's "3 ELA ApS T3" and in the second it's "3 MA ApS T6" --- the next issue is that ELA and MA tests go to different locations in the template. I guess I need an if statement for that so that the destination range can be selected correctly.

How can I make this into a loop?

 

[SkipVought]

Hi,

I'd suggest using Data/Get External Data/New Database Query -- Excel files -- YOUR WORKBOOK -- YOUR WORKSHEET where you have the AutoFilter Table.

Perform the query to return the data WITH THE SAME CRITERIA AS THE FILTER, to H5.

No copy 'n' paste!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[PBAPaul]

What you might want to do is setup some arrays:

Dim MyRange(2) as string
Dim MyCrit(2) as string

MyRange(0) = "C2:I3"
MyBook(1) = "C4:I11"

MyCrit(0) = "3 ELA ApS T3"
MyCrit(1) = "3 MA ApS T6"

For X = 0 to 1
   Selection.AutoFilter Field:=2, Criteria1:=MyCrit(X)
    Range(MyRange(X)).Select
    Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Next

You will need to play around with this but I hope that it will give you the basic idea.

 

[LTillner]

SKip,

I'm using one template, that will be written as different tabs in the final workbook (one tab for each grade level)

Your idea is the best, like you said no copy and paste. I will have to set the autofilter though for different criteria with each grade level, subject (English, Math, Science) and each test number....

To do that, am I back to needing to filter on each test (the lowest grouping level) and create named ranges then
get those named ranges with the Get External Data (etc) commands?

 

[SkipVought]

WHY?????

are you putting similar data on different tabs???

This severely complicates things.

You can NEVER view more than ONE tab. Isn't that just like a FILTER?

So why go to the bother and EXPENSE of multiplying a simple process???

Put ALL your data in one sheet and filter it as required, report by report.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[LTillner]

SKip,


You and I have discussed this before . All I'm doing is creating the final "pretty" spreadsheet of data. This is the way the data will be seen by Management. It's the way they want to see it. The actual data is all in one place, in fact, it's on the mainframe. All I'm doing is making "pretty reports for the guys upstairs!"

The final workbook, needs to be saved the way the printed report looks -- just like it's a piece of paper only.

That's the requirement. I'll just keep plugging away with the cut and paste method I think. It's less of a learning curve,

Thanks for your input.

 

[SkipVought]

That's the point that I tell my manager to do what he/she does best and I'll do what I do best.

It does not change the requirement to present various views of data on one sheet rather than multiple sheets,

SINCE...

you can ONLY view ONE SHEET AT A TIME!

Oh well!
[tt]
I
D
e
s
c
end. Ahhhh[sub]hhhhhhhhhhhhhh. I feel better now.[/sub][/tt]

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[LTillner]

I understand your point. However, I can't change this dynamic.... I'm just the contract person trying to automate some legacy reports. They like what they like.

Sorry that it pushes your buttons!