Creating an order

[Closed]
Report
-
Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
-
Hello,
I have a multi sheet database where each sheet represents a vendor. I'd like to transfer the information from the row, Column A, B, C, to my Order Sheet when I input a value In Column Quantity. Thanks
Jeremy


1 reply

Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
237
Hello Jeremy,

Assuming that the quantities will go into Column D and Sheet1 is your Order sheet, the following code may help:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

        If Target.Value <> "" Then
        Target.EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
        End If
  
Sheet1.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code is a Worksheet_Change event and needs to be placed into each sheet module. To implement the code, right click on the sheet tab and from the menu that appears, select "view code". In the big white field that then appears, paste the above code. You will need to do this for each source sheet.

When you place a value in any cell in Column D of any sheet and then move to the next cell, the relevant row of data will be automatically transferred to sheet1.

Following is the link to my test work book which I envision your work book to look like:-

https://www.dropbox.com/s/a6nx09la3c3v3of/Jeremy%28Worksheet_Change%20event%2C%20all%20shts%29.xlsm?dl=0

Place a value in any cell in Column D of any sheet (except Sheet1) then click away (or press Enter or down arrow) for the relevant row of data to be transferred to Sheet1.

I hope that this helps.

Cheerio,
vcoolio.