TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by tarun@1964 »

Hi, respected “ADMINISTRATORS and FORUM MEMBERS” hope all of You are well.
I want to transfer DATA (Item Code and Item Name) from ITEM RECEIVED SHEET by clicking SAVE Button to ITEM LIST SHEET frequently. If found same Item Code and same Item Name exist previously in this sheet then no data will be transfer from ITEM RECEIVED SHEET while item receiving in ITEM RECEIVED SHEET and again I want to transfer DATA (Item Code and Qty) from ITEM RECEIVED SHEET by clicking SAVE Button to SUPPLIER LIST SHEET frequently after every invoice entry complete.
NB: Above mentioned in ITEM RECEIVED SHEET there is only used 4 no’s of column for Item Name. what will be the code if found more than four item in any INVOICE.
I have code as below for this. But it is not working properly, and confirm that there is must be fatal mistake in the coding which I have used.

Code: Select all

Sub Save_Data()
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:L").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
' Copy Item Name and Quantity
For s = 1 To 4
wt.Cells(t, 2 * s + 2).Value = ws.Range("E" & s + 2).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("F" & s + 2).Value
wt.Range("M" & t).Value = ws.Range("X3").Value
Next s
'Copy Date
wt.Range("B" & t).Value = ws.Range("A3").Value
'Copy Invoice No
wt.Range("C" & t).Value = ws.Range("B3").Value
'Copy Company Name
wt.Range("D" & t).Value = ws.Range("C3").Value
 
' Find first empty row in columns A:D on ITEM LIST Sheet
t = wt.Range("B: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
If You kindly look at my problem, then hopefully in this matter I will get the solution to this problem You have. I am eagerly wait for Your kind cooperation.
Yours sincerely
tarun@1964

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by HansV »

Welcome to Eileen's Lounge!

Which columns contain the Item Code and Item Name?
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by tarun@1964 »

Respected Mr. HansV Sir ,
In ITEM RECEIVED SHEET
Column 'D', 'H', 'L' , 'P' for Item Code
Column 'E', 'I' , 'M' , 'Q' for Item Name
Column 'F', 'J', 'N', 'R' for Item Qty

In ITEM LIST SHEET
Column B for Item Code
Column C for Item Name

In SUPPLIER LIST SHEET
Column ' E' , 'G', 'I', 'K' for Item Name
Column 'F', 'H', 'J', 'L' for Item Qty

Thanking You
Yours Sincerely
tarun@1964

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by HansV »

Could you attach a copy of the workbook without sensitive data? Maximum size 250 KB.
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by tarun@1964 »

Respected Mr. HansV Sir ,
as per Your Instruction here i attached Workbook. if i want to add ITEM in any INVOICE then may i populate extra COLUMN in ITEM RECEIVED Sheet and SUPPLIER LIST Sheet ?


Thanking You
Yours Sincerely
tarun@1964
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by HansV »

Here is a new version of the macro. You can run it after adding one or more rows in the ITEM RECEIVED sheet.

Code: Select all

Sub Save_Data()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim s As Long
    Dim m As Long
    Dim c As Long
    Dim t As Long

    Application.ScreenUpdating = False

    ' Source sheet
    Set ws = Worksheets("ITEM RECEIVED")
    ' Find last used row
    m = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    ' Copy new items to ITEM LIST sheet
    Set wt = Worksheets("ITEM LIST")
    ' Find last used row in column B on ITEM LIST Sheet
    t = wt.Cells(wt.Rows.Count, 2).End(xlUp).Row
    ' Loop through columns D, H, L and P on ITEM RECEIVED sheet
    For c = 1 To 4
        ' Loop through the rows
        For s = 3 To m
            ' Item Code filled in?
            If ws.Cells(s, 4 * c).Value <> "" Then
                ' Is it a new one?
                If wt.Range("B2:B" & t).Find(What:=ws.Cells(s, 4 * c).Value, LookAt:=xlWhole) Is Nothing Then
                    ' Copy Item Code and Item name to new row
                    t = t + 1
                    wt.Cells(t, 2).Resize(1, 2).Value = ws.Cells(s, 4 * c).Resize(1, 2).Value
                End If
            End If
        Next s
    Next c

    ' Copy transactions to SUPPLIER LIST sheet
    Set wt = Worksheets("SUPPLIER LIST")
    ' Find last used row in column C on SUPPLIER LIST Sheet
    t = wt.Cells(wt.Rows.Count, 3).End(xlUp).Row
    ' Loop through rows on ITEM RECEIVED sheet
    For s = 3 To m
        ' New invoice number?
        If wt.Range("C2:C" & t).Find(What:=ws.Cells(s, 2).Value, LookAt:=xlWhole) Is Nothing Then
            ' Copy data
            t = t + 1
            ' Date, invoice no, supplier name
            wt.Cells(t, 2).Resize(1, 3).Value = ws.Cells(s, 1).Resize(1, 3).Value
            ' Item name and quantity
            For c = 1 To 4
                wt.Cells(t, 2 * c + 3).Resize(1, 2).Value = ws.Cells(s, 4 * c + 1).Resize(1, 2).Value
            Next c
            wt.Cells(t, 13).Value = ws.Cells(s, 24).Value
        End If
    Next s

    Application.ScreenUpdating = True
End Sub
See the attached version. I assigned the macro to the SAVE shape.

Book1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by tarun@1964 »

Respected Hans Vogelaar Sir,
excellent, awesome, it is working perfectly, I have no language to thank You, but I want to thank You. I made a request to You in the last post my problem that this VBA code will work if i want to increase the number of items (more than FOUR Item) I mean if I want to increase the number of columns for more items? Again Many Many Thank You
Yours Sincerely
tarun@1964

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by HansV »

If you add more columns in both the ITEM RECEIVED and SUPPLIER LIST sheets, you only need to change both of the lines

For c = 1 To 4

in the code. For example, if you add columns for a 5th and 6th item, use

For c = 1 To 6
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: TRANSFER DATA FROM A SPREADSHEET TO TWO SPREADSHEET AT A TIME

Post by tarun@1964 »

Respected Hans Vogelaar Sir, as per Your instruction i have changed some places in VBA Code given by You, as given below

'For c = 1 To 6' instead of 'For c = 1 To 4'

' Loop through columns D, H, L, P, T and X on ITEM RECEIVED sheet

instead of

' Loop through columns D, H, L, and P, on ITEM RECEIVED sheet


wt.Cells(t, 17).Value = ws.Cells(s, 32).Value

instead of

wt.Cells(t, 13).Value = ws.Cells(s, 24).Value

Thanking You
Yours Sincerely
tarun@1964