update data from sheets

ageordieinneed

New Member
Joined
Jun 20, 2019
Messages
21
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I guess you are looking for this.


Excel 2013/2016
BCDEFGHIJ
4CodeNameOrdersD/O/OY/NNotesLEGDateTime
5100101102103104105106107108
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0

hi muzama

would the mean i would have to put the formulas in all columns or could there be a vba created?
cheers
 
Last edited:
Upvote 0
Hello Ageordieinneed,

Try the following code assigned to your update button:-


Code:
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.
 
Upvote 0
You're welcome and thanks for the feed-back.

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top