VBA Programming help
 

Hello,

I have an Excel file with 2 worksheets. One containing Order Info called
"Orders" and another named "Analysis" which contact some fixed data and two
columns for automatic fill in from the "Orders" sheet.

I would like to create a code or macro when execute, transfer the Order# and
Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the Qty
into account. so, if the order# has a quantity of 2, then it will fill in two
rows for one Order. The sample data is below. Thanks for any help.

Analysis sheet:
Line Plan Date Actual Date Order#
100 12/9/09
101 12/10/09
102 12/10/09
103 12/11/09
104 12/14/09
105 12/14/09
106 12/15/09
107 12/18/09
108 12/29/09

Orders Sheet:
Order# Prod Start Qty
167834 1/22/10 2
172916 2/1/10 2
169001 2/1/10 2
169003 2/2/10 2

RESULT of Analysis sheet:
Line Plan Date Actual Date Order#
100 12/9/09 1/22/10 167834
101 12/10/09 1/22/10 167834
102 12/10/09 2/1/10 172916
103 12/11/09 2/1/10 172916
104 12/14/09 2/1/10 169001
105 12/14/09 2/1/10 169001
106 12/15/09 2/2/10 169003
107 12/18/09 2/2/10 169003
108 12/29/09

VBA Programming help
 

Programming exercise, not tested

Sub t()
Dim wsa As Worksheet
Dim wso As Worksheet
Dim rng As Range
Dim c As Range
Dim lrow As Long

Set wso = Worksheets("orders")
Set wsa = Worksheets("analysis")

lrow = wso.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range(wso.Cells(2, 3), wso.Cells(lrow, 3))
With wsa
For Each c In rng
lrow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
.Cells(lrow, 3).Resize(c, 1) = c.Offset(-1, 0)
.Cells(lrow, 4).Resize(c, 1) = c.Offset(-2, 0)
Next c
End With
End Sub

"Cam" wrote in message
...
Hello,

I have an Excel file with 2 worksheets. One containing Order Info called
"Orders" and another named "Analysis" which contact some fixed data and
two
columns for automatic fill in from the "Orders" sheet.

I would like to create a code or macro when execute, transfer the Order#
and
Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the
Qty
into account. so, if the order# has a quantity of 2, then it will fill in
two
rows for one Order. The sample data is below. Thanks for any help.

Analysis sheet:
Line Plan Date Actual Date Order#
100 12/9/09
101 12/10/09
102 12/10/09
103 12/11/09
104 12/14/09
105 12/14/09
106 12/15/09
107 12/18/09
108 12/29/09

Orders Sheet:
Order# Prod Start Qty
167834 1/22/10 2
172916 2/1/10 2
169001 2/1/10 2
169003 2/2/10 2

RESULT of Analysis sheet:
Line Plan Date Actual Date Order#
100 12/9/09 1/22/10 167834
101 12/10/09 1/22/10 167834
102 12/10/09 2/1/10 172916
103 12/11/09 2/1/10 172916
104 12/14/09 2/1/10 169001
105 12/14/09 2/1/10 169001
106 12/15/09 2/2/10 169003
107 12/18/09 2/2/10 169003
108 12/29/09

VBA Programming help
 

Hi KC,

I tried your code and got this error when I ran the macro.
"Run-time error '1004':
Application-defined or object-defined error"

And the Analysis sheet returns like this:
Line Plan Date Actual Date Order#
100 12/9/09 Qty
101 12/10/09 Qty
102 12/10/09
103 12/11/09
104 12/14/09
105 12/14/09
106 12/15/09
107 12/18/09
108 12/29/09

What is causing this? Thanks

"KC" wrote:

Programming exercise, not tested

Sub t()
Dim wsa As Worksheet
Dim wso As Worksheet
Dim rng As Range
Dim c As Range
Dim lrow As Long

Set wso = Worksheets("orders")
Set wsa = Worksheets("analysis")

lrow = wso.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range(wso.Cells(2, 3), wso.Cells(lrow, 3))
With wsa
For Each c In rng
lrow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
.Cells(lrow, 3).Resize(c, 1) = c.Offset(-1, 0)
.Cells(lrow, 4).Resize(c, 1) = c.Offset(-2, 0)
Next c
End With
End Sub

"Cam" wrote in message
...
Hello,

I have an Excel file with 2 worksheets. One containing Order Info called
"Orders" and another named "Analysis" which contact some fixed data and
two
columns for automatic fill in from the "Orders" sheet.

I would like to create a code or macro when execute, transfer the Order#
and
Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the
Qty
into account. so, if the order# has a quantity of 2, then it will fill in
two
rows for one Order. The sample data is below. Thanks for any help.

Analysis sheet:
Line Plan Date Actual Date Order#
100 12/9/09
101 12/10/09
102 12/10/09
103 12/11/09
104 12/14/09
105 12/14/09
106 12/15/09
107 12/18/09
108 12/29/09

Orders Sheet:
Order# Prod Start Qty
167834 1/22/10 2
172916 2/1/10 2
169001 2/1/10 2
169003 2/2/10 2

RESULT of Analysis sheet:
Line Plan Date Actual Date Order#
100 12/9/09 1/22/10 167834
101 12/10/09 1/22/10 167834
102 12/10/09 2/1/10 172916
103 12/11/09 2/1/10 172916
104 12/14/09 2/1/10 169001
105 12/14/09 2/1/10 169001
106 12/15/09 2/2/10 169003
107 12/18/09 2/2/10 169003
108 12/29/09



.