Macro to copy last row in range

[tonywatsonhelp]

Hi Everyone,

I add my sales into sheet "Quotes" Range is D27:O51

The data for each sale is two rows,
row1 the details,
row2 Notes, (Not all sales have notes)

so what I want is a macro that will copy the last "Row1" sales details and paste it below but it needs to place it 2 rows down to miss the notes row.

So if the last row was 29, it would copy and paste into row 31 etc.
now to complicate things more, I only want to copy columns D to K and O

I've attached a table to try show what my sheet looks like. Please help if you can thanks Tony

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

26

Part

Type

number

Price

Qant

sold

%

date

country

area

time

Person

27

Details:

A1

R

12

22

1

Y

100

2018

UK

NW

12

TW

28

Notes:

this row

does not count as

the last row and

is

not

copied

29

Details:

S3

E

11

22

1

Y

100

2017

USA

NNT

11

TW

30

Notes:

31

Details:

D2

Q

111

11

2

N

100

2016

UK

NW

1

TW

32

Notes:

33

Details:

P9

P

9

9

99

Y

99

2018

UK

N

2

TW

OK, so this is the last detals row so this would be copied

34

Notes:

THERE MIGHT BE

NOTES

HERE

IT MIGHT BE BLANK

35

Details:

copied

row

would

go

here

36

Notes:

37

Details:

38

Notes:

39

<tbody>
</tbody>

 

[davesexcel]

Find the 1st empty row in column d and see what is beside it, if it is "Notes:" then use the row below.

Sub Button1_Click()
    Dim LstRw As Long, r As Range
    
    LstRw = Cells(Rows.Count, "D").End(xlUp).Row + 1
    
    If Range("C" & LstRw) = "Notes:" Then LstRw = LstRw + 1
    
    Set r = Range("D" & LstRw & ":K" & LstRw + 1, "O" & LstRw & ":O" & LstRw + 1)
    
    r = "=IF(R[-2]C="""","""",R[-2]C)"
    r.Value = r.Value

End Sub

 

[Fluff]

How about

Sub GetLastDetails()
   Dim Fnd As Range
   
   Set Fnd = Range("D" & Rows.Count).End(xlUp)
   If Fnd.Offset(, -1).Value <> "Details:" Then Set Fnd = Fnd.Offset(-1)
   Fnd.Resize(, 12).Copy Fnd.Offset(2)
   Fnd.Offset(2, 8).Resize(, 3).ClearContents
End Sub

 

[tonywatsonhelp]

Thanks to both of you,
both great ideas and worked first time, so i've gone from no solution to two
thank you very much
Tony

 

[Fluff]

Glad we could help & thanks for the feedback