Getting my Range Selection Right

[Boots6]

Hello,

I'm writing this macro to cycle and calculate our monthly insurance contribution:

Application.Goto Reference:="Ending_Balance"
Selection.Cut
Range("AF3").Select
ActiveSheet.Paste
Application.Goto Reference:="Ending_Hours"
Selection.Cut
Range("AD3").Select
ActiveSheet.Paste
Range("AE3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-25]"
Range("AE3").Select
Selection.Copy
Range("AE4:AE200").Select
ActiveSheet.Paste
Range("AG3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>=140,RC[-1]+RC[-26]-RC[-28],RC[-1]-14)"
Range("AG3").Select
Selection.Copy
Range("AG4").End(xlUp).Select
ActiveSheet.Paste

"Ending_Balance" and "Ending_Hours" are just long sections of those columns to ensure it will always copy as we hire new employees. When I move them over and have the formulas run through, I only want to copy the formulas (AE3 & AG3) down as far as there are rows on the sheet so I don't have a bunch of N/A cells at the bottom. I've tried doing .End(xlDown), but since the cells are empty, it doesn't work obviously. Is there a way to do this using Offset? I tried using Offset(0,-10), but it took me to the bottom of the sheet. How do I paste from cell AE4 to the last row of an empty column using another column as a reference for the last row? Thanks!

 

[SkipVought]

Hi,

so I don't have a bunch of N/A cells at the bottom

What VERSION of Excel?

You should NOT have UNUSED ROWS PRE-LOADED WITH FORMULAS!!! What a mess!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

depending on your column that you want to reference...let's assume it is A

dim low as long

lRow = cells(1,"A").end(xldown).row
cells(3,"AE").copy destination:= range(cells(4,"AE"),cells(lRow,"AE"))
cells(3,"AG").copy destination:= range(cells(4,"AG"),cells(lRow,"AG"))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Boots6]

All I needed was this:

Range("S3").Select
Selection.Copy
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("S4:S" & LastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Thanks though.

 

[SkipVought]

Actully is is preferable NOT to select anything, but rather reference the objects...

  with Range("S3")
    .Copy .end(xldown).offset(1) 
  end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}