NO REPLY FOR MY PROBLEM

Contributor

Although I posted the matter as given below on 20-05-2022 and no response yet from Respected "MICROSOFT TECH COMMUNITY TEAM MEMBERS", and hope I will get assist in this regards.
Hi Respected COMMUNITY TEAM MEMBERS, hope all of You are well. my subject is that, I have an Excel Workbook and I unable to access that Excel work book just as I want by using VBA Code. Actually I have a Work book containing THREE SPREADSHEET.The name of stated SPREADSHEET respectively 1) ITEM RECEIVED Sheet , 2) ITEM LIST Sheet, 3) SUPPLIER LIST Sheet. I want to Transfer schedule DATA after entering DATA in ITEM RECEIVED Sheet and by pressing existing SAVE BUTTON exist in ITEM RECEIVED Sheet in TWO other SpreadSheet named ITEM LIST Sheet, and SUPPLIER LIST Sheet at a time simultaneously from ITEM RECEIVED Sheet from which data would be transferred. I use CODE And I confirm that there is must be fatal mistake in the coding which I have used. again I want to say when if found ITEM CODE and ITEM NAME is already there in ITEM LIST Sheet and if the same thing is adopted again in nexttime 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. CODE as given below . May I get assist in this regards.

Sub SaveData()
Dim wsItemRecd As Worksheet
Dim wtSupplierList As Worksheet
Dim wtItemList As Worksheet
Dim s As Long
Dim t As Long
Dim r As Long

Application.ScreenUpdating = False

Set wsItemRecd = Worksheets("ITEM RECEIVED")
Set wtSupplierList = Worksheets("SUPPLIER LIST")
Set wtItemList = 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 = wtSupplierList.Range("E:L").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wtSupplierList.Cells(t, 2 * s + 2).Value = wsItemRecd.Range("E" & s + 2).Value
wtSupplierList.Cells(t, 2 * s + 3).Value = wsItemRecd.Range("F" & s + 2).Value
wtSupplierList.Range("M" & t).Value = wsItemRecd.Range("X3").Value

Next s

'Copy Date

wtSupplierList.Range("B" & t).Value = wsItemRecd.Range("A3").Value

'Copy Invoice No

wtSupplierList.Range("C" & t).Value = wsItemRecd.Range("B3").Value

'Copy Company Name

wtSupplierList.Range("D" & t).Value = wsItemRecd.Range("C3").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 = wtItemList.Range("A:C").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
wtItemList.Cells(t, 2 * s + 2).Value = wsItemRecd.Range("D" & s + 2).Value
wtItemList.Cells(t, 2 * s + 3).Value = wsItemRecd.Range("E" & s + 2).Value

Next s

Application.ScreenUpdating = True

End Sub

Sheets Picture as given below which as I want

ITEM LIST Sheet

Sr. NoItem CodeItem NameSales Price 
 AB1ABCD  

SUPPLIER LIST Sheet

Sr. NoDateInvoice NoSupplier NameItem NameQtyItem NameQtyItem NameQtyItem NameQtyInvoice Value
 26-05-20221BANGURABCD1EFGH3IJKL5MNOP7103

ITEM RECEIVED Sheet

                   
 

 

SAVE
    
DATEINVOICE NOSUPPLIER NAMEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICESUB TOTALTAXVALUE INCL TAXDISCINVOICE VALUE
26-05-20221BANGURAB1ABCD12AB2EFGH34AB3IJKL56AB4MNOP781005%1052103
                        
5 Replies
Hello I can solve your problem but please connect with me on google meet and will solve the issue in meeting
RESPECTED Devendra Jain Sir, first of all i want to thank You for Your offer to solve my ISSUE.but with great sorrow i would like to inform You that i will not able to connect/communicate with You on GOOGLE MEET in any way as directed by You.although i understand that i could not explain my issue to You exactly. i may resubmit my ISSUE/Problem again what i want to solve on the basis Your Prior instruction. Thank You Very Much again for Your Trying
TARUNKANTI1964
so I'm trying to understand the problem. Is it that your code fails/errors in the case of a duplicate or that your code will add a duplicate line and you don't want that? Since it appears, based on my read-over of your code, that it will just create a new line for every new received item, I suspect that is the "issue". If you don't want to create a repeat then you just need to add a loop (or search) to check for if it already exists.
You also say " I have an Excel Workbook and I unable to access that Excel work book just as I want by using VBA Code". I don't understand what you mean by this.
Is this your code or did someone else write this code and you are trying to tweak/fix it?

Respected@ mtarler Sir ,

I had posted my problem "DATA TRANSFERRING IN TWO EXCEL SHEET AT A TIME" by pressing SAVE BUTTON on 17-05-2022 using VBA CODE as given below.

Sub SaveData()

Dim ws As Worksheet
Dim wt As Worksheet
Dim wt As Worksheet

Dim s As Long
Dim t As Long
Dim r As Long

Application.ScreenUpdating = False

Set ws = Worksheets("ITEM RECEIVED")
Set wt = Worksheets("SUPPLIER LIST")
Set wt = Worksheets("ITEM LIST")

' Find first empty row in columns A:M on SUPPLIER LIST Sheet

t = wt.Range("A:J").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

' Copy Item Name and Item Quantity

For s = 1 To 4

wt.Cells(t, 2 * s + 2).Value = ws.Range("F" & s + 2).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 2).Value
wt.Range("M" & t).Value = ws.Range("X5").Value

Next s

'Copy Date

wt.Range("B" & t).Value = ws.Range("A5").Value

'Copy Invoice No

wt.Range("C" & t).Value = ws.Range("B5").Value

'Copy Company Name

wt.Range("D" & t).Value = ws.Range("C5").Value

' Find first empty row in columns A:C on ITEM LIST Sheet

t = wt.Range("A:C").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

' Copy Item Code and Item Name

For s = 1 To 4

wt.Cells(t, 2 * s + 2).Value = ws.Range("D" & s + 2).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("E" & s + 2).Value

Next s

Application.ScreenUpdating = True

End Sub


The Above stated code I have used for a Work book for transferring Data at a time in to Sheet2 and Sheet3 simultaneously from Sheet1 by pressing SAVE Button from which data would be transferred. And I confirm that there is must be fatal mistake in the coding which I have used. when if found SUPPLIER NAME and ITEM NAME is already there in Sheet2 and if the same
thing is adopted in next in Sheet1 with different Invoice No then ITEM CODE and ITEM NAME will not be repeat in Sheet2, as per row wise in Sheet1) ITEM RECEIVED Unfortunately, above
stated code not work properly. Is it possible for transferring schedule data in both sheet at a time by pressing SAVE BUTTON? May I get help? With regards.
on the same date I got VBA Code from our respected "COMMUNITY TEAM MEMBER " Mr. Subodh_Tiwari_sktneer Sir as below

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:J on SUPPLIER LIST Sheet
t = wsSupplier.Range("A:J").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

wsSupplier.Cells(t, 2 * s + 2).Value = wsItemRecd.Range("F" & s + 2).Value

wsSupplier.Cells(t, 2 * s + 3).Value = wsItemRecd.Range("E" & s + 2).Value

wsSupplier.Range("M" & t).Value = wsItemRecd.Range("X5").Value

Next s

'Copy Date

wsSupplier.Range("B" & t).Value = wsItemRecd.Range("A5").Value

'Copy Invoice No

wsSupplier.Range("C" & t).Value = wsItemRecd.Range("B5").Value

'Copy Company Name

wsSupplier.Range("D" & t).Value = wsItemRecd.Range("C5").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 used the same above in my project but it not works properly. On 26-05-2022 I posted the same problem in caption "NO REPLY FOR MY PROBLEM" and ther is no incident of TWEAK/FIX.
ultimately i am now feeling that it is completely the matter of mother tongue. actually my mother tongue is BENALI , I live in BENGAL of INDIA and i understand that i cannot
explain my problem to YOU properly.i am presenting the sheet picture as whst i want.

Pic. ITEM LIST Sheet

Sr. NoItem CodeItem NameSales Price
 AB1ABCD 
 AB2EFGH 
 AB3IJKL 
 AB4MNOP 
 AB6QRST 
 AB7UVWX 
    
if found same Item Code and same Item Name adopted in ITEM RECEIVED Sheet with different invoice no. then  Item Code and Item Name not repeat in ITEM LIST SHEET

 

Pic. SUPPLIER LIST Sheet

 

Sr. NoDateInvoice NoSupplier NameItem NameQtyItem NameQtyItem NameQtyItem NameQtyInvoice Value
 01-04-20201KOLKATAABCD1EFGH3IJKL5MNOP7103
 02-04-20202MUMBAI  EFGH9IJKL11  105.1
 03-04-20203DELHIABCD4  IJKL9MNOP14180.7
 04-04-20204CHENNAI  EFGH5  MNOP19178.6
 05-04-20205BANGALOREABCD1  IJKL17  107.2
 18-06-202031PUNEQRST11UVWX11    275.2

Pic. ITEM RECEIVED Sheet

                   
 

 

SAVE
    
DATEINVOICE NOSUPPLIER NAMEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICEITEM CODEITEM NAMEQTYUNIT PRICESUB TOTALTAXVALUE INCL TAXDISCINVOICE VALUE
01-04-20201KOLKATAAB1ABCD12AB2EFGH34AB3IJKL56AB4MNOP781005%1052103
02-04-20202MUMBAI    AB2EFGH94AB3IJKL116    1025%107.12105.1
03-04-20203DELHIAB1ABCD42    AB3IJKL96AB4MNOP1481745%182.72180.7
04-04-20204CHENNAI    AB2EFGH54    AB4MNOP1981725%180.62178.6
05-04-20205BANGALOREAB1ABCD12    AB3IJKL176    1045%109.22107.2
18-06-202031PUNEAB6QRST1110        AB7UVWX11142645%277.22275.2
Respected mtarler Sir,
I am not getting any reply regarding above stated matter yet. please look after my matter. hope i will get Your kind assistance.

thanking You