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
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.
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?
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.
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?
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.
