Getting to the bottom of it

[tbmarlie]

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!

 

[ManicMiner17]

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.Offset(1, 1),
LastCell.Offset(1, 6))
Application.CutCopyMode = False

End Sub

Ask if there is anything you don't follow

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastCell As Range

Set wks = ActiveSheet

With wks
Set LastCell = .Cells(.Rows.Count, "E").End(xlUp)
End With

LastCell.Offset(1, 0).Resize(1, 7).FormulaR1C1 = "=sum(r3c:r[-1]c)"

End Sub

I like to start at the bottom and work to the top of the worksheet. I avoid
empty rows that way.

With wks
Set LastCell = .Cells(.Rows.Count, "E").End(xlUp)
End With

Since .offset(1,0) comes down one row. And .resize(1,7) says to resize that
original cell to 1 row by 7 columns.

Then it applies the same formula (summing the data that starts in row 3 and goes
to the cell above the formula) to all these 7 cells.

 

[Dave Peterson]

ps. You have another response to your earlier post, too.

 

[tbmarlie]

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.Offset(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

 

[Gpompidou]

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

Try this:

Sub Copy_To_Right_v2()
Const colStr = "E" 'You assign column letter.
Dim rBeg As Integer, rEnd As Integer
Dim rng As Range

rBeg = 2 'You which row to start.
rEnd = Cells(Rows.Count, colStr).End(xlUp).Row
Set rng = Range(Cells(rBeg, colStr), Cells(rEnd, colStr))

rng.Copy rng.Range("B1:G1")
rng.Range("B1:G1").EntireColumn.AutoFit

End Sub

 

[ManicMiner17]

Hi tbmarlie,

Sorry I didn't get back before, just got in:

This is all one line which should explain the syntax error:

ActiveSheet.Paste _
Destination:=Worksheets("Sheet1").Range(LastCell.Offset(1, 1), _
LastCell.Offset(1, 6))

Having said that I saw the code Dave Peterson posted; much more elegant
than mine and shorter to boot!

I'd use that especially as he explained it well.

Good Luck.