Changeable references - how best to handle?

[Gingertrees]

Doing a daily report, in which I import 2 sheets to be processed, we'll call ClassReport and GradeData.
Both have a varying number of rows, but have the same general setup each day. Say I want to pull in grade percentage for each student into Class Report, doing a vlookup referencing Grade Data.

Trying to automate this all into VBA, so please bear with me:

ClassReport:
[TABLE="width: 400"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]vlookup-goes-here[/TD]
[TD]"[/TD]
[TD]"[/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Etc.[/TD]
[TD]Jenny (C2)[/TD]
[TD]Sam(D2)[/TD]
[TD]Amy(E2)[/TD]
[TD]Chris(F2)[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]etc.[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

GradeData:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Assign[/TD]
[TD]ment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]Name[/TD]
[TD]% to date[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Amy[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Chris[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Dani[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Jenny[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Marc[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Sam[/TD]
[TD]78[/TD]
[/TR]
</tbody>[/TABLE]

(Sure there's only one kid with each name in the class - roll with it.)

Sometimes there's 15 rows in Grade Data, other times there's 20, etc.

So I put my vlookup in Class Report:
=vlookup(C2,'GradeDataSheet8-18'!B4:C9,2,False)

I usually just fill across, so I have to add Absolute references after I type that. (...$B$4:$C$9)

Two questions:
1) Since I'm trying to automate this into a macro, how would I establish the range in GradeDataSheet every day? If it was the same thing in the same sheet, I'd define the name as "GRADES" and sub that into my vlookup.

I already have figured out how to do a LastRow and LastColumn declaration on there, so I could use that, but how do I get my code to recognize that today's sheet is the 8-18 one?

2) If I am able to answer question #1, how can I use Absolute references with variables like LastColumn (an integer)?

 

[Gingertrees]

Think I made it too complicated, but it won't let me edit it now. I think I'll try a smaller, bite-sized question and go from there...