PDA

View Full Version : Solved: Search column a for value select and past rows to next sheet



coserria
08-03-2009, 04:44 AM
OK here is my task. I am in charge of issuing materials to new employees.

I have an excel 2003 form that gives me the document. The information that I’m required to track is populated in to the top of the excel worksheet “WHSE REQ” that data is in columns A1:R17. It is populated by formula to this range, and the amount of row that is used is variable. My goal is to have the form on a push button macro.

I have been doing this via hand copy and paste. I have one button that give me a new document number and prints the copies that I need. I want to have a Button that pastes the data that I need to save into the Register for my records, flat database.

The trouble that I would have it How to use code to search the Range A1;R17 and to find the row that have the value 10 in column A if the value is 10 copy these rows to the first empty row on the second sheet “Issue register” however it need to paste the data as values.

I can record simple code but have no ability to write the variable code i'm tryign to learn but time is not on my side. that is why I need more time savers. My coworker that is as good as most of you is getting married and on his honeymoon, so no help from him

mdmackillop
08-03-2009, 04:54 AM
With Sheets("WHSE REQ")
.Range("A1:R17").AutoFilter Field:=1, Criteria1:="10"
.Range("A2:R17").SpecialCells(xlCellTypeVisible).Copy
Sheets("Issue registor").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
.Range("A1:R17").AutoFilter
End With

coserria
08-03-2009, 05:22 AM
Cheers, perfectly, and I can even understand it so I can do that myself in the future.