On Jul 27, 2:30*pm, ManicMiner17 wrote:
On 27/07/2010 22:11, tbmarlie wrote:



I’m reposting my question since I messed up on the phrasing the first
time.

I need to figure out how to get to cell E154 which is the last cell in
a number of rows, But, which will be changing, because the number of
rows will be changing every time I create this spreadsheet. *If I
start at Cell E3 and then put in code to get to the very last cell (in
this case, it just happens to be E154), I'm assuming it would be
something like the following:

Dim Rng As Range
* * *Set Rng = Range("E3").End(xlDown)

But, once I have this range, I'm not sure of the code to actually get
me there using this range
Then, once I’ve gotten to that last cell (E154) I have the following
vb code to copy this footer (which was used to sum the contents of the
column above it) and paste it to the 6 cells
to the right.

This is what I have so far but I need to make it relative from the
starting point.

Range("E154").Select
* * *Selection.Copy
* * *Range("F154:L154").Select
* * *ActiveSheet.Paste

Thanks!

Hi tbmarlie,

This is short on explanation:

Sub AAA()
Dim Firstcell As Range
Dim LastCell As Range

Set Firstcell = Range("E3")
Set LastCell = Cells(Rows.Count, 5).End(xlUp)

LastCell.Offset(1, 0) = "=Sum(" & Firstcell.Address(False, False) & ":"
& LastCell.Address(False, False) & ")"
LastCell.Offset(1, 0).Copy
ActiveSheet.Paste
Destination:=Worksheets("Sheet1").Range(LastCell.O ffset(1, 1),
LastCell.Offset(1, 6))
Application.CutCopyMode = False

End Sub

Ask if there is anything you don't follow- Hide quoted text -

- Show quoted text -

The compiler doesn't seem to like the := after Destination.
Specifically, it is saying Compile error: Expected Expresssion. Thanks