Forum Discussion
Data transfer in to two spreadsheet at a time
Hi, Respected COMMUNITY TEAM MEMBERS, hope all of You are well. I have an Excel Workbook along with three spreadsheet Named 1) ITEM RECEIVED Sheet, 2) ITEM RECEIVED Sheet, 3) SUPPLIER LIST Sheet. Actually I want to transfer schedule DATA into other TWO sheets named ITEM LIST Sheet and SUPPLIER LIST Sheet at a time simultaneously from ITEM RECEIVED Sheet by pressing SAVE Button on ITEM RECEIVED Sheet. when if found ITEM CODE and ITEM NAME is already there in ITEM LIST Sheet and if the same thing is adopted again in next in ITEM RECEIVED Sheet with different Invoice No. then ITEM CODE and ITEM NAME will not be repeat in ITEM LIST Sheet, as per row wise. I used VBA Code as below but it not working properly, And I confirm that there is must be fatal mistake in the coding which I have used. May I get assist in this regards.
Sub SaveData()
Dim wsItemRecd As Worksheet
Dim wsSupplier As Worksheet
Dim wsItemList As Worksheet
Dim s As Long
Dim t As Long
Dim r As Long
Application.ScreenUpdating = False
Set wsItemRecd = Worksheets("ITEM RECEIVED")
Set wsSupplier = Worksheets("SUPPLIER LIST")
Set wsItemList = Worksheets("ITEM LIST")
' Copy Item Name and Item Quantity
For s = 1 To 4
' Find first empty row in columns A:M on SUPPLIER LIST Sheet
t = wsSupplier.Range("D:L").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wsSupplier.Cells(t, 2 * s + 2).Value = wsItemRecd.Range("E" & s + 2).Value
wsSupplier.Cells(t, 2 * s + 3).Value = wsItemRecd.Range("F" & s + 2).Value
wsSupplier.Range("M" & t).Value = wsItemRecd.Range("X4").Value
Next s
'Copy Date
wsSupplier.Range("B" & t).Value = wsItemRecd.Range("A4").Value
'Copy Invoice No
wsSupplier.Range("C" & t).Value = wsItemRecd.Range("B4").Value
'Copy Company Name
wsSupplier.Range("D" & t).Value = wsItemRecd.Range("C4").Value
' Copy Item Code and Item Name
For s = 1 To 4
' Find first empty row in columns A:C on ITEM LIST Sheet
t = wsItemList.Range("A:C").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wsItemList.Cells(t, 2 * s + 2).Value = wsItemRecd.Range("D" & s + 2).Value
wsItemList.Cells(t, 2 * s + 3).Value = wsItemRecd.Range("E" & s + 2).Value
Next s
Application.ScreenUpdating = True
End Sub
I want as below
ITEM RECEIVED Sheet
| |||||||||||||||||
| ITEM CODE | ITEM NAME | QTY | UNIT PRICE | ITEM CODE | ITEM NAME | QTY | UNIT PRICE | ITEM CODE | ITEM NAME | QTY | UNIT PRICE | SUB TOTAL | TAX | VALUE INCL TAX | DISC | INVOICE VALUE | |
| AB2 | EFGH | 3 | 4 | AB3 | IJKL | 5 | 6 | AB4 | MNOP | 7 | 8 | 100 | 5% | 105 | 2 | 103 |
ITEM LIST Sheet
| Sr. No | Item Code | Item Name | Sales Price | |
| AB1 | ABCD |
SUPPLIER LIST Sheet
| Sr. No | Date | Invoice No | Supplier Name | Item Name | Qty | Item Name | Qty | Item Name | Qty | Item Name | Qty | Invoice Value |
| 24-05-2022 | 1 | BANGUR | ABCD | 1 | EFGH | 3 | IJKL | 5 | MNOP | 7 | 103 |