add lines to a order list

[vitorjose]

Hello i am hoping that you can provide some help.

I have a workbook with 2 worksheets -

Workbook A contains a record of custoemr orders - one order per row -
columns H to M contains the qty of a specifec product (each column
represents a differenct product) -

Customer Name.............Product type A....Product Type B.....Product
Type C
Company A............................
3........................3........................3...........

Workbook B - contains a record of each item ordered from workbook A -
A customer who orders qty 3 of 3 different items (Columns H to M in
workbook A) would have 9 records - multiples of a single item would
each have there own row - with a different id

"Company A" - "Product Type A " - No 1
"Company A" - "Product Type A " - No 2
"Company A" - "Product Type A " - No 3
"Company A" - "Product Type B " - No 1
"Company A" - "Product Type B " - No 2
"Company A" - "Product Type B " - No 3
"Company A" - "Product Type C " - No 1
"Company A" - "Product Type C " - No 2
"Company A" - "Product Type C " - No 3


My issue is that i would like Workbook B to automatically update
whenever a new order is entered in Workbook A - Someone would enter in
information and then initiate a macro that would take the info for
that order and create entries on Workbook B -

As a further enhancement - it would also be great if changes to an
existing order (Workbook A) could also be reflected in Workbook B

Thanks in advance for your help

Victor P

 

[Dan R.]

Victor,

This is no small task but this should get you started. Right click
your "Workbook A" tab and hit 'View Code', then paste this in there:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim custLookup As Range, lRow As Long
If Target.Cells.Count > 1 Or _
Target.Value = "" Then Exit Sub
With Sheets(2)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Select Case Target.Column
Case Is = 1
Set custLookup = Sheets(2).Columns("A").Find( _
What:=Target.Value, _
LookIn:=xlValues, _
MatchCase:=False)
If custLookup Is Nothing Then
Target.Copy Sheets(2).Cells(lRow + 1, 1)
Else
If MsgBox("This customer already exists. " & _
"Would you like to add them again?", _
vbYesNo) = vbYes Then
Target.Copy Sheets(2).Cells(lRow + 1, 1)
End If
End If
Case Is = 2
' do stuff
End Select
End Sub

 

[vitorjose]

Dan

Thanks i will give this a try

Victor,

This is no small task but this should get you started. Right click
your "Workbook A" tab and hit 'View Code', then paste this in there:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim custLookup As Range, lRow As Long
   If Target.Cells.Count > 1 Or _
      Target.Value = "" Then Exit Sub
   With Sheets(2)
      lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
   End With
   Select Case Target.Column
      Case Is = 1
         Set custLookup = Sheets(2).Columns("A").Find( _
               What:=Target.Value, _
               LookIn:=xlValues, _
               MatchCase:=False)
         If custLookup Is Nothing Then
            Target.Copy Sheets(2).Cells(lRow + 1, 1)
         Else
            If MsgBox("This customer already exists. " & _
                  "Would you like to add them again?", _
                  vbYesNo) = vbYes Then
               Target.Copy Sheets(2).Cells(lRow + 1, 1)
            End If
         End If
      Case Is = 2
         ' do stuff
   End Select
End Sub