PDA

View Full Version : Event code help - copy row to another sheet



airla
10-18-2006, 06:12 PM
While I have used Excel for years... I am very new to macros and VBA (very new)

Our entire office is struggling with this cumbersome spreadsheet that we use for purchasing. We have to sift throgh a ton of products and select those which we want to purchase by entering a quantity in the appropriate column. Then sift through it again and either write out a list of products by hand or copy and paste them into another sheet to print and send out to vendors for quotes.

What we would "like" it to do is when a # is typed into the "quantity" column, the rows information would automatically copy to the next row in another sheet set up as a quote sheet.

Looking at the spreadsheet might help with understanding what we want.

Any help would be greatly appreciated!

johnske
10-18-2006, 08:26 PM
Hi airla,

Welcome to VBAX. I've edited your thread title to reflect what you actually need (i.e. code for a worksheet event that will do what you want) this should better get the attention of those able to help - something that just says (basically) "Help" doesn't help in getting it :)

Regards,
John

airla
10-19-2006, 04:55 PM
Thanks! Wasn't sure what to call it. :P

Bob Phillips
10-19-2006, 05:05 PM
Every row (in your example they are identical) or just the first?

airla
10-20-2006, 09:59 PM
We choose which items to purchase by placing a quantity in the Quantity "L" column of the appropriate row. We only need to copy rows we choose to purchase. The rows are not identical. Each product has a "parent part #" and additional part numbers within that family are numbered accordingly. For instance part # ABC0123 could have a "bulk #" (ABC0123B), several "package #'s" (ABC0123Q5, ABC0123Q10) etc. I'm not sure exactly what you are asking here, but I hope that answered your question.

Norie
10-21-2006, 10:55 AM
airla

This should be possible but the structure of the data is going to make it harder than it really should be.

You have XX's in various rows in column A.

Is it these rows you want to trigger the code?

In the example you are taking the size from row 6, I assume when an entry is made in row 5 of column L.

Or is it when an entry is made in row 14?

airla
10-21-2006, 07:12 PM
Sigh, this may not even be possible with all of the crazy variables in this spreadsheet.

Here's a new example of the spreadsheet with some comments added to try to explain how this thing works.

Each product has a "Parent Part #" (row 4 in the example), a packaged part # (row 5 in the example) and a bulk part # (row 9 and 11) in the example.

Let's say we will enter a quantity in row 11 since most parts are ordered with their bulk part #.

The problem is this... we could also have a "Q25" part #, an "MCB" part # and several other variations for each part. In other words there are not always the same # of rows for each "parent part #" ( I don't know who set up this spreadsheet but it is FAR from efficient.)

Let's just say that in a perfect world we would need to pull the part # and description from row 4: column A, the size from row 6: column A, and the Quantity from Row 11: column L.

One thing that may help with this is that there is conditional formatting set and the rows we need to copy are a certain color. The description is always the purple color and the bulk # we order from is always teal.

acw
10-22-2006, 08:22 PM
Hi

There are heaps of assumptions surrounding this, but see if it gets you going.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim OutSH As Worksheet
Dim NextRow As Integer
Dim FindDesc, FindSize, FindItem
If Target.Count = 1 And Target.Column = 12 And Len(Target.Value) > 0 Then
Set OutSH = Sheets("Quote Sheet")
NextRow = OutSH.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
FindItem = Left(Target.Offset(0, -11).Value, Len(Target.Offset(0, -11)) - 1)
Set FindDesc = Range("A:A").Find(what:="Part # " & FindItem & "*", lookat:=xlWhole)
Set FindSize = Range("A:A").Find(what:="*" & FindItem, lookat:=xlWhole)
OutSH.Cells(NextRow, 2).Value = FindDesc.Value
OutSH.Cells(NextRow, 3).Value = FindSize.Value
OutSH.Cells(NextRow, 4).Value = Target.Value

End If

End Sub



Tony

airla
10-26-2006, 06:21 PM
Thought I should let you know this generates a runtime error and doesn't copy anything.

Thanks for the try though

SamT
10-26-2006, 07:47 PM
Are you willing to restructure that spreadsheet to make it easier to code, faster to run, and possible to maintain, while at the same time developing a smooth process for selecting items to order?

:think::think::think:

acw
10-26-2006, 08:34 PM
HI

Ran OK for me.

Where did you put the code, and what did you change to invoke it?


Tony

airla
10-27-2006, 05:24 PM
re you willing to restructure that spreadsheet to make it easier to code, faster to run, and possible to maintain, while at the same time developing a smooth process for selecting items to order?


Unfortunately, our AS400 consultant built it and info is exported from AS400 to this spreadsheet. Restructuring isn't an option.


Where did you put the code, and what did you change to invoke it?

I tried placing it as a "module" ... did nothing, Placed it directly into the worksheet code... it tried to run when a quantity was entered and resulted in an error. Debugging highlighted the 3rd to the last line. If I am doing it wrong, please explain what to do. Like I said, this is all very new to me.

SamT
10-27-2006, 08:55 PM
Unfortunately, our AS400 consultant built it and info is exported from AS400 to this spreadsheet. Restructuring isn't an option.

Leave that workbook as an AS400 data receiver, Use VBA to Cut and Paste its' data into your own well structured worksheet and run your reports on the good sheet.

Does he just dump Data in all those columns but the "This Action" Range?

What columns do you actually look at when ordering?

I'm thinking a sheet with Part#-Desc, Suggested Qty, and This Action Columns. Build a modeless Form to show the info you might want to see that opens when you select a Suggested Qty cell. Select another cell to close it.

I've got a little macro that will run down a row and create a fully qualified range name on each label. It creates a SheetName!RangeName := Column under label.

After that you can change the labels as you please, Move columns around, whatever. Colorcode a parts cell if Stock On Hand gets too low. . . . . . .

Then, For each nm in Names; GoodSheet!nm = OtherSheet!nm

Now code GoodSheet to make it really easy to place orders.

SamT

acw
10-29-2006, 03:36 PM
Hi

This has to be put in the sheet event code area.

Right click on the Main Spreadsheet tab, select view code, and put the code there.

Based on you sample file, change the number in L11 and it will move the details to Quote Sheet.


Tony

mdmackillop
10-30-2006, 02:02 PM
This uses the Part Description format colour preceeding the Bulk number to create the quote.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 12 Then Exit Sub
MakeQuote Target
End Sub

Sub MakeQuote(Target As Range)
Dim Bulk As String, Part As String
Dim Rw As Long, i As Long
Dim NextQuote As Range
Rw = Target.row
Bulk = Cells(Rw, 1)
For i = Rw To 3 Step -1
'ConditionalColor - see "Get Cell Color Function" KB by byundt
If ConditionalColor(Cells(i, 1), "interior") = 37 Then
Part = Cells(i, 1)
Exit For
End If
Next
Set NextQuote = Sheets("Quote Sheet").Cells(Rows.Count, 2).End(xlUp).Offset(1)
NextQuote = Bulk
NextQuote.Offset(, 1) = Part
NextQuote.Offset(, 2) = Target.Value
End Sub