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

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 the

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

--
Dan


On Dec 20, 2:58 pm, vitorjose wrote:
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

Thanks i will give this a try



On Dec 20, 6:18*pm, "Dan R." wrote:
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 the

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

--
Dan

On Dec 20, 2:58 pm, vitorjose wrote:

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