The simple explanation...

In Excel 2002, I need to select/highlight the cells for column M
through U on whatever row I'm on - the row varys from month to month so
it has to be dynamic and so far all I've figure out in VBA is
hardcoding...

Also, if you could include the way to go down one row from the bottom
that would solve most of the static grand total line problems around
the office. :)


Extra info...

I'm running a report in Excel every month. It pulls the data from an
SQL Query which varies from month to month in length. Since I'm trying
to remove all human hands from this process I'm trying to get Excel to
recognize the size each time it runs. The data is a solid block in the
first 12 columns (A-L) and columns M-U are used for data entered later
and formulas. So I'm working on this part of the macro so it will copy
the formulas in M2 through U2, highlight M2 (or 3) through U(last row)
and paste in the copied formulas. Here's the code I'm trying:

Sub DynamicPasting2()
Range("M2:U2").Select << Cells with formulas
Selection.Copy
Range("L2").Select << Far right column with data on every row
Selection.End(xlDown).Select
Range("M207:U207").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
End Sub

Obviously, The trouble is the Range("M207:U207").Select line - I need
it to select the M through U cells on the bottom row - which isn't
always 207. I guess I just don't know how to select without absolute
references in VBA.

Thanks,
Jordan


---
Message posted from http://www.ExcelForum.com/

You can do this with a single line of code (it may be wordwrapped on
screen):

Range("M2:U2").Copy Range(Range("L2"), Range("L2").End(xlDown)).Offset(0,
1)

This is an example of a macro doing something programmatically rather than
in terms of how you do it manually. After you wrestle with a few of these
you'll get the hang of it.

--
Jim Rech
Excel MVP