Forum Discussion
NO REPLY FOR MY PROBLEM
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. 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 |
| 26-05-2022 | 1 | BANGUR | ABCD | 1 | EFGH | 3 | IJKL | 5 | MNOP | 7 | 103 |
ITEM RECEIVED Sheet
| ||||||||||||||||||||||||
| 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 | |
| 26-05-2022 | 1 | BANGUR | AB1 | ABCD | 1 | 2 | AB2 | EFGH | 3 | 4 | AB3 | IJKL | 5 | 6 | AB4 | MNOP | 7 | 8 | 100 | 5% | 105 | 2 | 103 | |
- 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?
