Delete/Move rows

[ganjo]

I have a sheet that holds my inventory and has 3 colums. The colums are serialnumber, techid and date. I also have a sheet for each techid. Is there a way to delete/move a row of data if the serialnumber is entered on another sheet.

example.

I have a serialnumber added to my first sheet. I go to a techid sheet and enter that same serial number. Is there a way to copy the entire row the same serialnumber is on and copy to the current sheet? Possibly a search function where I enter a serialnumber and it finds the same thing on the main sheet and copies the rows into the current sheet.



Thanks for any help.

[mrice]

Try pasting the following into the ThisWorkbook tab in the VBA editor (Alt F11)

Dim Working As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Working = True Then Exit Sub
Working = True
TechID = Sh.Name
For Each Cell In Target
If Application.CountIf(Sheets("Sheet1").Columns(1), Cell) > 0 Then
Sheets("Sheet1").Rows(Sheets("Sheet1").Columns(1).Find(Cell, , xlValues, xlWhole).Row).Copy Destination:=Cell.EntireRow
End If
Next Cell
Working = False
End Sub



I've assumed that you main data is stored on Sheet1

[Pharm_hand]

If you have the techid list on another tab, you could also make use VLOOKUP to cross reference the technician. If you copy the VLOOKUP over onto several columns, you can then pull in several fields of related data from the techID table.

[ganjo]

Wow your good!!

I changed the copy command to cut but is there a way to make it remove the blank row in sheet1?
Also is there a way to allow me to add them back into sheet1?

I removed the techID column as I have a sheet for each tech. Seemed pointless to have it.

And thanks alot, this should help out quite a bit.

I tried using VLOOKUP but I wasnt having much luck.

[ChemistB]

For Each Cell In Target
If Application.CountIf(Sheets("Sheet1").Columns(1), Cell) > 0 Then
Sheets("Sheet1").Rows(Sheets("Sheet1").Columns(1).Find(Cell, , xlValues, xlWhole).Row).Copy Destination:=Cell.EntireRow
End If
Next Cell

Hi MRice, I'm a newbie at VBA and am learning through this board. I'm trying to figure out how this would work...
Does Target refer to Sheet 1 or the Sheet you're on (TechID)?
The "xlValues" is automatically known from the present cell in TechID?
When would the "CountIf" statement give you a value of "0"?

Thanks
ChemistB

[mrice]

Target actually refers to the cells that have had their values changed. I used a for-each loop just in case multiple cells were filled at the same time.

The countif function could give a zero if the value for the serial number wasn't on the first sheet at all. In this case the subsequent lines where it tries to find the row on which the serial number occurs would go into error. It's just a bit of code to make things a bit more robust.

Xlvalues tells the FIND function to do a match on the cells value as opposed to formula or comment.

Hope this helps.

PS. Interested in the user name - I'm a chemist as well.

[mrice]

To delete the source row, you need to keep a record of the row number

Dim Working As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Working = True Then Exit Sub
Working = True
TechID = Sh.Name
For Each Cell In Target
If Application.CountIf(Sheets("Sheet1").Columns(1), Cell) > 0 Then
SourceRow = Sheets("Sheet1").Columns(1).Find(Cell, , xlValues, xlWhole).Row
Sheets("Sheet1").Rows(SourceRow).Copy Destination:=Cell.EntireRow
Sheets("Sheet1").Rows(SourceRow).Delete
End If
Next Cell
Working = False
End Sub

[ganjo]

Thanks again for the help.

Is it possible to make this work on any sheet opposed to just sheet1? Then I could have another sheet for all completed work and move them out of the tech sheet.

[ChemistB]

Thanks Martin,

I'm going to play around with this a bit until I understand it.

ChemistB
Yes, I also am a chemist, working for a J&J company in a QA Lab.

[mrice]

Yes - just substitute the name of the sheet that you want to use between the quotation marks.