Creating an order

Closed
Jeremy - Aug 25, 2016 at 12:50 PM
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
- Aug 27, 2016 at 09:20 AM
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

vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Aug 27, 2016 at 09:20 AM
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.
0