add lines to a order list
 

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

add lines to a order list
 

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

add lines to a order list
 

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