Previous Topic Next Topic

How to transfer data from sheet to sheet 2

Closed
Herisha - Aug 21, 2019 at 09:57 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 3, 2019 at 12:01 PM
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
Related:

5 responses

Answer 1 / 5
Herisha Posts 5 Registration date Thursday August 22, 2019 Status Member Last seen August 29, 2019
Aug 22, 2019 at 08:46 PM
0
Answer 2 / 5
Herisha Posts 5 Registration date Thursday August 22, 2019 Status Member Last seen August 29, 2019
Aug 22, 2019 at 08:51 PM
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.
0
Answer 3 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 27, 2019 at 12:08 PM
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
0
Herisha Posts 5 Registration date Thursday August 22, 2019 Status Member Last seen August 29, 2019
Aug 27, 2019 at 08:48 PM
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.
0
Herisha Posts 5 Registration date Thursday August 22, 2019 Status Member Last seen August 29, 2019
Aug 27, 2019 at 08:49 PM
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 29, 2019 at 12:21 PM
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
0
Herisha Posts 5 Registration date Thursday August 22, 2019 Status Member Last seen August 29, 2019
Aug 29, 2019 at 11:29 PM
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
0
Answer 4 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Sep 3, 2019 at 12:02 PM
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
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
Anonymous User
Updated on Aug 22, 2019 at 07:11 AM
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.
-1