VBA Code to activate the tab and and transfer data

[Yeshika44]

Hi There

I have to create a Lab Inventory workbook. There are 11 tabs including Master tab. The story is when new items come in, the user must enter the information into the Master Tab (Column A to G), and when the item is transferred to another lab, the person must enter rest of the information into Column H to M. The transfer lab name is selected from a drop-down menu in column "M". My requirement is when the person selects a lab name (Lab 01) from column “M”, specific data (Column A to G) from that row should be moved to the Lab 01 tab, and the same procedure should be followed when transferring items from Lab 01 to another lab.

I am not much knowledgeable with VBA. I would greatly appreciate if anyone can help me to write a VBA code for this. I found following code to use to activate the tab (Lab name) when selected from the column “M” but I don’t have any clue how to transfer data in the same time to the same tab. My spreadsheet is attached here.


>>> use code - tags <<<

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
 If Not (Application.Intersect(Range("M3:M2000"), Target) Is Nothing) Then _
 ThisWorkbook.Sheets(Target.Value).Activate
End Sub

If any of you can help me with this would be much appreciated.

Many thanks

Yeshi

 

[p45cal]

specific data (Column A to G) from that row should be moved to the Lab 01 tab

If indeed the data is to be moved (not copied) what happens to the data on the same row in columns H and to the right, of the Master sheet?
Similarly, when moving from Lab to Lab what happens to data in the row to be moved beyond column G?