copy last row with formulas and paste into next row same sheet

[mgaz]

Hi, i want to copy the last row in my worksheet (always starts with a
date) and then paste it into the next row, then fixing the values in
the first row.
I want it to be dynamic but with the sub i have so far it only applies
to rows 533...?
can anyone help?


Sub Macro1
Range("A528:AY528").Select
Selection.Copy

Range("A534").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Application.Calculate
Range("A533:AY533").Select
Application.CutCopyMode = False
Selection.Copy

Range("A533").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[merjet]

Replace:

Range("A533:AY533").Select
Application.CutCopyMode = False
Selection.Copy

Range("A533").Select

With:
iEnd = Range("A65536").End(xlUp).Row - 1
Range("A" & iEnd & ":AY" & iEnd).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & iEnd).Select

I didn't test this. I believe the - 1 is needed because your earlier
code put something in row 534, but maybe not.

Hth,
Merjet