Autofill to last entry

[jtodd]

Hi
I have spread sheet with formuas in columns A , H,I ,J,K and L all diferent .
The user copys and pastes data into B-G , the amount of rows varies from 2000 rows upto 10000 rows .
I would like to have a macro ( or some kind of VBA) that would insert the formulas and autofill down to the last cell with data in .
The reason being with so many formuas ( one is indirect ) the spread sheet can become slow, so would help to only enter formulas that are needed .
Could then remove the formulas making it even quicker.

 

[Michael M]

What are the formulas ??

 

[jtodd]

Sorry I have not used that before - is this what you mean.

7099_TNs_MrExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Outstanding Tns 7099 06/08/2020			Lookup Ref	030820
2		Status Alert Key
3		Completed
4		0 - 9 days
5		10 - 12 days
6		13 days +
7		Date	Document Number	Sending Cost Centre	Product	Total Stock Quantity	Total Stock Average Cost Value	sku	RDC SPW plus Alert Status	Comments	No of days	Authority number	Trailer	Further info
8	421933644713 : PSP TORNADO R 7X	01/07/2020	421933	02428: KINGS LYNN	644713 : PSP TORNADO R 7X	1	£1,243	644713	RDC	paper only ref discreps	37	37
9	324609271657 : GOJ G24MAC20	10/07/2020	324609	00884: BARBICAN	271657 : GOJ G24MAC20	2	£8	271657	SPW	paper only ref discreps	28	28
10	421055249771 : GOG CHROMECAS T V4	17/07/2020	421055	07171: DIRECT CUSTOMER RETURNS	249771 : GOG CHROMECAS T V4	1	£22	249771	SPW		21	21
11	421063249951 : APP S342SGBSP	17/07/2020	421063	07171: DIRECT CUSTOMER RETURNS	249951 : APP S342SGBSP	1	£175	249951	SPW		21	21
12	325033273844 : APP AIRPODS P RO	18/07/2020	325033	07171: DIRECT CUSTOMER RETURNS	273844 : APP AIRPODS P RO	1	£150	273844	SPW		20	20
13	437861233497 : DJI TELLO	18/07/2020	437861	07171: DIRECT CUSTOMER RETURNS	233497 : DJI TELLO	1	£71	233497	SPW		20	20
14	472066219633 : SAM T5 500 PR TBLESSD	20/07/2020	472066	07171: DIRECT CUSTOMER RETURNS	219633 : SAM T5 500 PR TBLESSD	1	£71	219633	SPW		18	18
15	341154696638 : DJI DJI MVC A IR 2 C	21/07/2020	341154	07171: DIRECT CUSTOMER RETURNS	696638 : DJI DJI MVC A IR 2 C	1	£694	696638	SPW	sdfasdfasdfasdfasdfa	17	17
16	363588733665 : FTB VERSA2PIN K	21/07/2020	363588	07171: DIRECT CUSTOMER RETURNS	733665 : FTB VERSA2PIN K	1	£118	733665	SPW		17	17
Sheet2

Cell Formulas
Range	Formula
H8:H16	H8	=TEXT(LEFT(E8,6),REPT("0",6))
I8:I16	I8	=VLOOKUP(H8,SkuData!A:B,2,FALSE)
J8:J16	J8	=VLOOKUP(A8,(INDIRECT("'"&F$1&"'!A:K")),10,FALSE)
K8:K16	K8	=IF(B8="","",RIGHT($B$1,10)-B8)+1
L8:L16	L8	=IFERROR(VLOOKUP(A8,(INDIRECT("'"&F$1&"'!A:K")),11,FALSE),"")
M8:M16	M8	=IFERROR(VLOOKUP(A8,(INDIRECT("'"&F$1&"'!A:K")),12,FALSE),"")
N8:N16	N8	=IFERROR(VLOOKUP(A8,(INDIRECT("'"&F$1&"'!A:K")),13,FALSE),"")
A8:A16	A8	=C8&E8

Named Ranges
Name	Refers To	Cells
SkuData!RDC_SPW_SKUList.accdb	=SkuData!$A$1:$C$116675	I8:I16

 

[Michael M]

Once the new data has been pasted into the sheet, run this code
It inserts all the formulas, then converts them to values, raher than the formulas

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("A8:A" & lr).Formula = "=C8&E8"
Range("H8:H" & lr).Formula = "=TEXT(LEFT(E8,6),REPT(""0"",6))"
Range("I8:I" & lr).Formula = "=VLOOKUP(H8,SkuData!A:B,2,FALSE)"
Range("J8:J" & lr).Formula = "=VLOOKUP(A8,(INDIRECT(""'""&F$1&"" '!A:K"")),10,FALSE)"
Range("K8:K" & lr).Formula = "=IF(B8="""","""",RIGHT($B$1,10)-B8)+1"
Range("L8:L" & lr).Formula = "=IFERROR(VLOOKUP(A8,(INDIRECT("" '""&F$1&""'!A:K"")),11,FALSE),"""")"
Range("M8:M" & lr).Formula = "=IFERROR(VLOOKUP(A8,(INDIRECT(""'""&F$1&""'!A:K"")),12,FALSE),"""")"
Range("N8:N" & lr).Formula = "=IFERROR(VLOOKUP(A8,(INDIRECT(""'""&F$1&""'!A:K"")),13,FALSE),"""")"
With Range("A8:N" & lr)
    .Value = .Value
End With
End Sub

 

[jtodd]

Many thanks for your time , all works well thankyou , I am learning all the time
One question , how could i allso add a border round each cell that is coppied down .

 

[Michael M]

Many thanks for your time , all works well thankyou , I am learning all the time
One question , how could i allso add a border round each cell that is coppied down .

Sorry...which cells...all the cells that contain a formula??
That seems a bit of a waste of resources??

 

[jtodd]

Ok thanks for replying , will check with the end user what they want it to looke like .

Again many thanks for your time

 

[Michael M]

Keep in mind that anything you use to highlight cells, will need to be removed every time new data is pasted in