Formula or VBA?

[ccameron]

Hi Everyone,
I was wondering if someone could help? I have a table of data that I would like to copy the last 2 rows only to another sheet. The table range is 'Defect Total' A40:N79, each week the data is updated so I would like to use a formula or macro that will copy the last 2 rows and paste the data into sheet1 A1:N2. any suggetions would be great.
With the table below I would want to copy and paste A71:K72, the following week I would want A72:K73.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
68	6/04/2011	1614	0	21	10	0	213	27	445	129	654
69	13/04/2011	1548	0	22	12	0	210	27	438	119	640
70	20/04/2011	1614	0	20	14	0	193	31	443	151	651
71	4/05/2011	1468	0	24	14	0	206	31	414	146	538
72	11/05/2011	1393	0	23	12	0	148	31	416	145	529
73	18/05/2011
74	25/05/2011
75	1/06/2011
Defects Total

Excel 2007



Thankyou
ccameron

 

[VoG]

I'm confused as to whether it is K or N. This is for K

Sub Cpy()
Dim LR As Long
With Sheets("Defect Total")
    LR = Range("B" & Rows.Count).End(xlUp).Row
    .Range("A" & LR - 1).Resize(2, 11).Copy Destination:=Sheets("Sheet1").Range("A1")
End With
End Sub

 

[ccameron]

Thanks Vog,
It is for N.
I appreciate the help.

ccameron

 

[VoG]

Try

Sub Cpy()
Dim LR As Long
With Sheets("Defect Total")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("A" & LR - 1).Resize(2, 14).Copy Destination:=Sheets("Sheet1").Range("A1")
End With
End Sub

 

[ccameron]

Thanks Vog,
It works well.


Regards
ccameron

 

[Peter_SSs]

I would like to use a formula or macro

For a formula approach (this would have the advantage of automatically updating when a new row is added to the 'Defects Total' sheet) try this in A1 of Sheet1 and copy across to N1 and down to row 2. (You may subsequently need to format A1:A2 as Date)

=INDEX('Defects Total'!A:A,MATCH(9.99E+307,'Defects Total'!$B:$B)-2+ROWS(A$1:A1))

 

[ccameron]

Thanks Peter,
I have used the code Vog supplied and added a button on the sheet and assigned the macro. But I will have a look at yours it may come in handy anyway.

Thanks again,

ccameron