Forum Discussion
NO REPLY FOR MY PROBLEM
- TARUNKANTI1964May 27, 2022Brass ContributorRESPECTED 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- mtarlerMay 27, 2022Silver Contributorso 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?- TARUNKANTI1964Jun 01, 2022Brass Contributor
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 WorksheetDim s As Long
Dim t As Long
Dim r As LongApplication.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").ValueNext 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).ValueNext 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 belowSub SaveData()
Dim wsItemRecd As Worksheet
Dim wsSupplier As Worksheet
Dim wsItemList As Worksheet
Dim s As Long
Dim t As Long
Dim r As LongApplication.ScreenUpdating = False
Set wsItemRecd = Worksheets("ITEM RECEIVED")
Set wsSupplier = Worksheets("SUPPLIER LIST")
Set wsItemList = Worksheets("ITEM LIST")
' Copy Item Name and Item QuantityFor 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 + 1wsSupplier.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").ValueNext 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 + 1wsItemList.Cells(t, 2 * s + 2).Value = wsItemRecd.Range("D" & s + 2).Value
wsItemList.Cells(t, 2 * s + 3).Value = wsItemRecd.Range("E" & s + 2).ValueNext 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. No Item Code Item Name Sales Price AB1 ABCD AB2 EFGH AB3 IJKL AB4 MNOP AB6 QRST AB7 UVWX 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. No Date Invoice No Supplier Name Item Name Qty Item Name Qty Item Name Qty Item Name Qty Invoice Value 01-04-2020 1 KOLKATA ABCD 1 EFGH 3 IJKL 5 MNOP 7 103 02-04-2020 2 MUMBAI EFGH 9 IJKL 11 105.1 03-04-2020 3 DELHI ABCD 4 IJKL 9 MNOP 14 180.7 04-04-2020 4 CHENNAI EFGH 5 MNOP 19 178.6 05-04-2020 5 BANGALORE ABCD 1 IJKL 17 107.2 18-06-2020 31 PUNE QRST 11 UVWX 11 275.2 Pic. ITEM RECEIVED Sheet
SAVE DATE INVOICE NO SUPPLIER NAME ITEM CODE ITEM NAME QTY UNIT PRICE 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 01-04-2020 1 KOLKATA AB1 ABCD 1 2 AB2 EFGH 3 4 AB3 IJKL 5 6 AB4 MNOP 7 8 100 5% 105 2 103 02-04-2020 2 MUMBAI AB2 EFGH 9 4 AB3 IJKL 11 6 102 5% 107.1 2 105.1 03-04-2020 3 DELHI AB1 ABCD 4 2 AB3 IJKL 9 6 AB4 MNOP 14 8 174 5% 182.7 2 180.7 04-04-2020 4 CHENNAI AB2 EFGH 5 4 AB4 MNOP 19 8 172 5% 180.6 2 178.6 05-04-2020 5 BANGALORE AB1 ABCD 1 2 AB3 IJKL 17 6 104 5% 109.2 2 107.2 18-06-2020 31 PUNE AB6 QRST 11 10 AB7 UVWX 11 14 264 5% 277.2 2 275.2