Related:
- How to transfer data from sheet to sheet 2
- Excel transfer data between sheets - How-To - Excel
- Transfer data between sheets in excel ✓ - Forum - Excel
- Automatically transfer data from one sheet to another in excel - How-To - Excel
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
- Transfer Data From One Excel Sheet to Another - How-To - Excel
5 replies
Herisha
- 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.
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.
TrowaD
- Posts
- 2693
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 18, 2021
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
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
Herisha
- 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.
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.
TrowaD
- Posts
- 2693
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 18, 2021
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
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
TrowaD
- Posts
- 2693
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 18, 2021
Hi Herisha,
Here is the code for a new supplier:
And here is the code for Update:
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
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
Subscribe To Our Newsletter!
