VBA: Autofill row in between rows.

[teatimecrumpet]

Hi,

I have a sheet where the rows 1 to x will change every time it is updated. The bottom row must stay as the bottom row as it totals all the rows above it.


What I'm trying to do is insert a row before the total row and then autofill that inserted row with the row directly above that.

I'm having trouble with the below code. Where after insertion of the row choosing the row for autofill into the row that was inserted....

ANY help is appreciated.

Thanks!

Sheets("NEW").Range("A1048576").End(xlUp).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("NEW").Range("A1048576").End(xlUp).Offset(-2, 0).EntireRow.AutoFill _
Destination:=DSRWKB.Sheets("NEW").Range("A1048576").End(xlUp).Offset(-1, 0), Type:=xlFillDefault

 

[teatimecrumpet]

Nobody?

 

[circledchicken]

Hi,

I'm not sure if this is exactly what you mean but here's how I interpret it:

• Copy the second last row (i.e. the one above the totals), and paste it above the totals. Here's how you could do that (any formula's etc would be copied through as well):

With Sheets("NEW").Range("A" & Rows.Count).End(xlUp)
    .Offset(-1, 0).EntireRow.Copy
    .EntireRow.Insert Shift:=xlDown
End With
Application.CutCopyMode = False

 

[teatimecrumpet]

It's been a while...

Circled Chicken thanks for the reply.

The copy function does work and the equations update. The problem with that is the first column was a date so when I autofill manually it pushes the date up by one. Copying and inserting leaves the new row with the copied date. The date is part of the calculations in the other row cells.

still couldn't the destination to offset from the source row.

I've since used a workaround by copying the row and inserting and then setting the date cell by another means.

But if anyone out there knows how to set the destination with an offset of the row I'd love to know

thanks,