update data from sheets

[ageordieinneed]

Hi guys
I have a spread sheet ( sheet 2) with a list of data customers name , so my columns have

B4=code , C4=Name , D4=ORDERS , E4=D/O/O , F4=Y/N , G4=NOTES , H4 = LEG , I4 =DATE, j4=TIME

How do I update the (sheet 1) Customers info and changing the order of columns when input new data on sheet 2 to the next empty row
D14=Code , E14=Name , F14=ORDERS , , G14=DATE, H14=TIME I14=NOTES, when I press the update button on sheet2?

cheers

 

[Sam_D_Ben]

I guess you are looking for this.

Excel 2013/2016
	B	C	D	E	F	G	H	I	J
4	Code	Name	Orders	D/O/O	Y/N	Notes	LEG	Date	Time
5	100	101	102	103	104	105	106	107	108
Sheet1

Cell Formulas
Range	Formula
B5	=Sheet2!B5
C5	=Sheet2!C5
D5	=Sheet2!D5
E5	=Sheet2!E5
F5	=Sheet2!F5
G5	=Sheet2!G5
H5	=Sheet2!H5
I5	=Sheet2!I5
J5	=Sheet2!J5

Muz

 

[ageordieinneed]

hi muzama

would the mean i would have to put the formulas in all columns or could there be a vba created?
cheers

 

[vcoolio]

Hello Ageordieinneed,

Try the following code assigned to your update button:-

Sub Test()

        Dim lr As Long, lr1 As Long, x As Long
        Dim cAr As Variant, pAr As Variant

Application.ScreenUpdating = False

        lr = Sheet2.Range("B" & Rows.Count).End(xlUp).Row
        lr1 = Sheet1.Range("D" & Rows.Count).End(xlUp).Row
        cAr = Array("B5:B" & lr, "C5:C" & lr, "D5:D" & lr, "I5:I" & lr, "J5:J" & lr, "G5:G" & lr)
        pAr = Array("D", "E", "F", "G", "H", "I")
        
Sheet1.Range("D15:I" & lr1).Clear

For x = LBound(cAr) To UBound(cAr)
        Sheet2.Range(cAr(x)).Copy
        Sheet1.Range(pAr(x) & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've used the sheet codes and not the sheet names in the code above.

I hope that this helps.

Cheerio,
vcoolio.

 

[ageordieinneed]

cheers guys

 

[vcoolio]

You're welcome and thanks for the feed-back.

Cheerio,
vcoolio.