I'm afraid I'm confusing the matter more then I should. I'm still unable to
get the code to function without getting the error. I'm sure I'm doing
something wrong as I'm not at all proficient in writing this type of code in
excel. Would it be possible for you to email me your spreadsheet that is
working so I can compare it to the one I'm using and recieving errors with?

Also, I'd be more then happy to email you the actual spreadsheet I'm
building so you could see exactly what it is I'm trying to do. If that would
help.

Thanks so much for the help thus far...I really appreciate your time. my
email address is

"gocush" wrote:

As for the error, the following is all ONE LINE OF CODE:

Set Dest = Sheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)

It only shows up in this forum as 2 lines. The code works for me.

Now, when you say that Col C does not exist, you need to be specific with
your information. Do you want the Item# to show up on Sheet2 ? If you do,
how does the program know which Job number you are referring to ?

Notice that in my example in Sheet1 I did not enter under Col C a Unit
number (-1, -2 ....). This part was generated automatically by the code,
when moving data to Sheet2

If you do not want an Item # in Sheet2 this is easy. Let me know

The method that I used performs the data transfer each time you enter a new
line of data. It can be modified to run with a command button after entering
several new lines. This code would then be moved to a standard module rather
than a Sheet object module. Which do you want?

"GreenMonster" wrote:

Getting closer....I think.

You stated that "Whenever you enter an item in Col C it will trigger this
event" based on the code you wrote. However Col C will only change when the
count changes. And when it does change it will change in increments of "one"
ex (A2001-1, A2001-2, A2001-3) That's the easy part to figure out for me.

Lets pretend that Col C doesn't exist (in reality it doesn't) and we're
working with just Col A (Qty) and Col B (Dimension) Sheet2 should populate a
specific number of line items that directly corresponds with the number
entered in ColA (Qty) of sheet1.

Also, I cut and past your code into sheet one and recieved the following
error in the debugger when attempting to enter data as you instructed "

'Send data to Job List
For i = 1 To lQty
Set Dest = Sheets("Sheet2").Cells(Rows.Count,

This happend whenever I entered a Qty (which will always be the first step
for me, the second step will be entereing the diminision, I'll never enter
data in A3(item).

"gocush" wrote:

The following should do what you have described ( with the following
modifications)

1. Enter data in Sheet1, which has

A B C
Qty Dimension Item
5 7x7 A201
2 4x4 A444
3 2x8 T343
4 5x8 A201
1 1x6 B222
4 3x9 T343

Sheet2 should look like this:

A B C D E
Job Unit Item Dim Completed

Then the following code goes in the Worksheet module for Sheet1.
Copy it
Activate Sheet1
Rt-click the sheet1 TAB
Click View code
Paste the code in the right panel

Whenever you enter an item in Col C it will trigger this event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lQty As Integer
Dim sDim As String
Dim i As Integer
Dim Dest As Range
Dim Jobs As Range
Dim oCell As Range

On Error GoTo ErrHandler
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
'Adjust the following range as needed
If Not Intersect(Target, Range("C2:C500")) Is Nothing Then
If Target.Offset(0, -2) < 1 Then
Target.ClearContents
Target.Offset(0, -2).Select
MsgBox "Please enter a Quantity."

Application.EnableEvents = True
Exit Sub
End If
If Target.Offset(0, -1) = "" Then
MsgBox "Need a Dimension."
Target.Offset(0, -1).Select
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
'Get the Qty and Dim
lQty = Target.Offset(0, -2)
sDim = Target.Offset(0, -1)

'Send data to Job List
For i = 1 To lQty
Set Dest = Sheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Target.Copy Dest
Dest.Offset(0, 3) = sDim
Dest.Offset(0, 2).FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Next

'Sort the Job List

Dest.CurrentRegion.Sort Key1:=Dest, Order1:=xlAscending,
Key2:=Dest.Offset(0, 1) _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

'Add the Unit #
Set Jobs = Sheets("Sheet2").Range("A2:A" & Dest.Row)
For Each oCell In Jobs
If oCell = oCell.Offset(-1, 0) Then
oCell.Offset(0, 1) = oCell.Offset(-1, 1) + 1
Else
oCell.Offset(0, 1) = 1
End If
Next oCell
End If
Application.EnableEvents = True
Exit Sub

ErrHandler:
Application.EnableEvents = True
End Sub




"GreenMonster" wrote:

thanks for your response - the prefix of the item number is a given (job
number) the extension at the end is a count number based on a series from the
number of units (i.e., unit XXX-01, is just the first item, -02 would be the
next in the series, and so on)...

the job number is assigned at the beginning and can be located in the data
file and the item number will drive from that based on the number of units.
if twelve units, there will be xxx-01 through xxx-12.

Does that help explain it a little better?

"gocush" wrote:

Repeating the Item 5 times or 2 times etc is the easy part. But knowing
which Item # would be a problem. I assume that sometimes the Item # could be
other than A201-xxx. How would we know if it were, say B201-x, or A301-x, or
A305-9 through A305-14 ?

Can you add to your explanation ?

"GreenMonster" wrote:

hmmm...I was afraid I didn't explain myself well enough. See if this makes
better sense.

I am trying to create an order entry workbook that will take input from the
order entry sheet and not only populate but create number of rows appropriate
to the number of items in the data entry sheet.

I have, for instance, the following data:

Quantity Dimensions Item #
5 7 x 7 A201-1 through A201-5
1 8 x 4 A201-6
2 9 x 3 A201-7 through A201-8

And so on€¦

I am trying to take this input data and populate other related tabs but not
with quantities, with individual items. For example,

Item # Dimensions Completed
A201-1 7 x 7 (left blank)
A201-2 7 x 7 (left blank)
A201-3 7 x 7 (left blank)
A201-4 7 x 7 (left blank)
A201-5 7 x 7 (left blank)
A201-6 8 x 4 (left blank)
A201-7 9 x 3 (left blank)
A201-8 9 x 3 (left blank)

The application is for manufacturing purposes for a schedule of
manufacturing tasks. I do not want it to be lumped into qty 5 or qty 2 as
this application is for a checklist of activities for factory workers that
they check off each individual item completed.

I am feeding several different spreadsheets with the workbook from this same
data source, so you can tell this is very manual intensive. Please help me
come up with the missing formula or macro or €¦.

Thanks.



"Tom Ogilvy" wrote:

Prefill the Pull sheet

=if('Data input'!A1="","",'Data input'!A1)

then drag fill down and across for as many cells as you might need.

--
Regards,
Tom Ogilvy

"GreenMonster" wrote in message
...
I'v built a spreadsheet that has multiple worksheets all of which retrieve
data from a central worksheet (we'll call this central sheet "data input")

The "data input" sheet is where the "quantity" of different items are
added
to the workbook. The worksheet that pulls data (we'll call this sheet
"data
pull") from the "data input" worksheet needs to identify the quantity of
items in the "data input" worksheet, then populate the "data pull"
worksheet
with that same number of rows.

In other words if Dat Entry has four rows of data, Data Pull will see the
item quantity as four and return four rows of data.

If you change the number of rows in Data Pull to 7, then Data Pull will
now
return 7 rows of data.

I hope this makes sense....if not email me at
and I'll try and explain further.

Thanks