Drop down menu selection that automatically shifts an entry to another sheet

[gfell153]

I am trying to develop a workbook for our business that will help us organize our production and inventory better. We have a family limestone business and every day our quarry superintendent sends over a sheet of production. The sheet contains a block number, dimensions, grade of stone, date it was taken up etc. I have developed a workbook that contains everything we need and in it I have included drop down menus for certain inputs. I was just wondering if there was a way to program it so when you select a certain value in the drop down list it will automatically shift it over to another sheet. For example if a block is shipped to our mill, I can go over to the production sheet and select MILL from my drop down list and it will move it over to another sheet that would contain all of our mill inventory. I'm not sure if this is a function Excel is capable of handling but I thought I would give it a shot. If not, is there another type of program similar to excel that would be able to handle this?

Thanks for your help,

Garrett

[Logit]

.
Paste this into a Routine Module :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E1")) Is Nothing Then
        Select Case Range("E1")
            Case "Sheet 2": Macro1
            Case "Sheet 3": Macro2
            Case "Sheet 4": Macro3
        End Select
    End If
End Sub

Sub Macro1()
    Sheets("Sheet2").Activate
    Sheets("Sheet2").Range("A1").Select
End Sub

Sub Macro2()
    Sheets("Sheet3").Activate
    Sheets("Sheet3").Range("A1").Select
End Sub

Sub Macro3()
    Sheets("Sheet4").Activate
    Sheets("Sheet4").Range("A1").Select
End Sub

Then you create a page list on your first sheet where it won't interfere with the remainder of your worksheet :

	P	Q	R
1		Sheet 2
2		Sheet 3
3		Sheet 4
4

Then say you click on cell E1, then on the Data Tab / Data Validation / and change ALLOW drop down to LIST. In the range field, you enter (in this scenario) Q1:Q3


See attachment.

[gfell153]

Man I think I'm so close but on my sheet in the drop down menu when I select a sheet it doesn't take me there. It just says sheet 2. Here I will attach my Excel file if I can figure out how to

[gfell153]

Here it is. I am trying to make it so the location column controls which sheet it goes to.

[Logit]

Two small issues (corrected in attached):

The macro code was still referencing cell E2 instead of I2.

The spelling of the sheet tab names was incorrect. Always make certain the spelling in the code is identical to whatever you are referencing. In this case
one of the tabs was Sheet4 ... instead of Sheet 4 (notice space between the word Sheet and the number 4.

And my apologies ... my instructions said to paste into a Routine Module. My mind was wandering ... I meant paste into the Sheet Level Module.

[gfell153]

Okay so is there a way to make all the information in that row transfer to the sheet I select?