Thanks very much, Tom. This will keep me from looking like the rookie I am!

"Tom Ogilvy" wrote in message
...
we forgot to dimension i
should read Dean forgot to dimension i



Dim i as Long
Dim rng as Range
if len(trim(Range("A1"))) = 0 then exit sub
Select Case lcase(Range("A1"))
Case "jane"
set rng = range("E12")
Case "joe"
set rng = Range("F10")
Case "moe"
set rng = Range("G8")
Case "curly"
set rng = Range("H6")
Case "bozo"
set rng = Range("I4")
Case "harpo"
set rng = Range("j2")
end Select
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=rng
Next i

--
Regards,
Tom Ogilvy


"Dean" wrote:

That works great except we forgot to dimension i, I assume as an integer.
Can you also help with the 2nd part of my request? I put it below the
macro
I copied in. I know this stuff should be easy. I'm just not that
comfortable with the VBA syntax yet.

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")
Next i

--
Regards,
Tom Ogilvy

"Dean" wrote:

Below are two iterations within my macro. Basically, it does this 14
times,
each time, changing the row reference by adding 9 more rows in the
first
line - that's all the difference you will see in the two sets of macro
text
below. Right now I have 14 of these, but I imagine I only need one,
if I
somehow build a loop to add 9 rows and repeat. Can you help me with
that?

Application.Goto Reference:="R7C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Application.Goto Reference:="R16C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Also, rather than making 10 copies of the above, I would like the
ColumnInput index parameter, currently "E12", for the data table to
change
based on what text I have in a certain worksheet cell, say cell A1 (of
the
current worksheet). For example, if cell a1 = "joe", rather than
"jane",
then the column reference in the data table should be cell F10, not
E12,
and
so on for about 6 choices in cell A1. Can you also help me
incorporate
such
a variable cell reference? This is independent of what I've asked for
in
the
paragraph above. In other words, this data table columninput would be
the
same for all 14 loops.

Thanks so much!
Dean