Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

How to transfer data from sheet to sheet 2 [Closed]

Report
-
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 -
Hello,

I have created an excel file which I need to track progress of each order, in the first sheet I have created the list of things that needs to be added to master file each time there is an update from the supplier and each supplier information should be gathered together Eg: 1 supplier has 7 task to complete, so each time someone select the supplier name from the sheet 1 the information should auto fill in master file for designated columns for the supplier it should not mix up with other supplier

secondly, how do I fill in new information like new supplier only after 8 rows every time?



System Configuration: Windows / Chrome 76.0.3809.100
See more

5 replies

Answer 1 / 5
Posts
5
Registration date
Thursday August 22, 2019
Status
Member
Last seen
August 29, 2019
Answer 2 / 5
Posts
5
Registration date
Thursday August 22, 2019
Status
Member
Last seen
August 29, 2019
Actually it's not like like copy paste data, this sheet will be used by multiple users, so each time there new supplier the information should be filled in new supplier field and transfer it to the second sheet every 7 rows

When there is update on the same supplier, we just need to choose the supplier from the drop down list and fill the update every blank row under the supplier name in the designated columns.
Answer 3 / 5
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
Hi Herisha,

So the screenshot you posted is the source sheet, where you input your data. What does your destination sheet look like?

Since you have buttons for New and Update, does that mean you can have duplicate suppliers with different data? Otherwise an Update button is all you need, right?

Best regards,
Trowa
Report
Posts
5
Registration date
Thursday August 22, 2019
Status
Member
Last seen
August 29, 2019
Hi Trowa,

The destination sheet looks like in the image attached.

Yes we have the same supplier with different data that's what the update button is for and the new will be the new supplier which is not added to the excel sheet yet. So every time there is an update on the existing supplier we will input the data and it should update automatically on the second sheet. I want to fill up the new supplier info every 9th row so 7 rows is blank. when ever there is update we just need to choose the supplier name under the drop down it will auto update in next blank row under the supplier name.
Report
Posts
5
Registration date
Thursday August 22, 2019
Status
Member
Last seen
August 29, 2019
Report
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
Hi Herisha,

Why do you want 7 blank rows between suppliers? If it is just to keep space for updated supplier data, we can also keep 1 row empty and when more data gets added, another empty row is added. That way you can see more data on your screen. Or is there a specific reason you want 7 empty rows?

Best regards,
Trowa
Report
Posts
5
Registration date
Thursday August 22, 2019
Status
Member
Last seen
August 29, 2019
Hi TrowaD,

Yes there is reason because each supplier has 7 task that need to be completed.

So each time when there is update it should flow into the blank rows into the supplier name

Regards,
Herisha
Answer 4 / 5
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
Hi Herisha,

Here is the code for a new supplier: 
Sub NewSupp()
Dim lRow, NewSuppRow As Long, Sheet2 As Worksheet

Set Sheet2 = Sheets("Blad2")

lRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
NewSuppRow = Application.WorksheetFunction.RoundDown((((lRow - 2) / 8) + 2), 0) * 8 - 6

Range("B2:B4").Copy
Sheet2.Range("A" & NewSuppRow).PasteSpecial Transpose:=True

Range("B10:B18").Copy
Sheet2.Range("D" & NewSuppRow).PasteSpecial Transpose:=True

Range("B2:B4", "B9:B18").ClearContents
End Sub


And here is the code for Update: 
Sub UpdateSupp()
Dim mFind As Range, Sheet2 As Worksheet, x As Long

Set Sheet2 = Sheets("Blad2")

Set mFind = Sheet2.Columns("C").Find(Range("B4"))

Do
    x = x + 1
    If x = 8 Then
        MsgBox "There is no room for a new update"
        Exit Sub
    End If
    If mFind.Offset(x, 0) = vbNullString Then Exit Do
Loop

Range("B2:B4").Copy
Sheet2.Range("A" & mFind.Row + x).PasteSpecial Transpose:=True

Range("B10:B18").Copy
Sheet2.Range("D" & mFind.Row + x).PasteSpecial Transpose:=True

Range("B2:B4", "B9:B18").ClearContents
End Sub


In both codes change the sheet reference on code line 4. This should be the name of the sheet where the data is copied to.

Let us know how this works out for you.

Best regards,
Trowa
Answer 5 / 5
Posts
1
Registration date
Thursday August 22, 2019
Status
Member
Last seen
August 22, 2019
kindly send me the screen shot then i tell you.but your question i think you should copy the all data and paste the other sheet.
Ask a question