Forum Discussion

TARUNKANTI1964's avatar
TARUNKANTI1964
Brass Contributor
May 23, 2022

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

            
 

 

SAVE
    
ITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICESUB TOTALTAXVALUE INCL TAXDISCINVOICE VALUE
AB2EFGH34AB3IJKL56AB4MNOP781005%1052103

ITEM LIST Sheet

Sr. NoItem CodeItem NameSales Price 
 AB1ABCD  

SUPPLIER LIST Sheet

 

Sr. NoDateInvoice NoSupplier NameItem NameQtyItem NameQtyItem NameQtyItem NameQtyInvoice Value
 24-05-20221BANGURABCD1EFGH3IJKL5MNOP7103
No RepliesBe the first to reply

Resources