pop up widow
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim insect As Range
Dim Y As String
Set insect = Intersect(Target, Range("B1:B10"))

If insect Is Nothing Then Exit Sub
Y = InputBox("What sheet do you want to put this in?")
Sheets(Y).Activate
End Sub

Thank you very much for the code, this is exactly what i was trying to
accomplish.
How about if you have a larger range to work with.
B1:B10
C1:C10
D1:D10
E1:E10
F1:F10
G1:G10
H1:H10
Thank you..

pop up widow
 

Try this,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim insect As Range
Dim Y As String
Set insect = Intersect(Target, Range("B1:H10"))

If insect Is Nothing Then Exit Sub
Y = InputBox("What sheet do you want to put this in?")
Sheets(Y).Activate
End Sub

Mike

"tleehh" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim insect As Range
Dim Y As String
Set insect = Intersect(Target, Range("B1:B10"))

If insect Is Nothing Then Exit Sub
Y = InputBox("What sheet do you want to put this in?")
Sheets(Y).Activate
End Sub

Thank you very much for the code, this is exactly what i was trying to
accomplish.
How about if you have a larger range to work with.
B1:B10
C1:C10
D1:D10
E1:E10
F1:F10
G1:G10
H1:H10
Thank you..

pop up widow
 

Hi,

I just checked you code and this is unlikely to work. The input box is
returning a string so sheets(y) would fail.

youe need

Sheets(CLng(Y)).Activate

Mike

"Mike H" wrote:

Try this,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim insect As Range
Dim Y As String
Set insect = Intersect(Target, Range("B1:H10"))

If insect Is Nothing Then Exit Sub
Y = InputBox("What sheet do you want to put this in?")
Sheets(Y).Activate
End Sub

Mike

"tleehh" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim insect As Range
Dim Y As String
Set insect = Intersect(Target, Range("B1:B10"))

If insect Is Nothing Then Exit Sub
Y = InputBox("What sheet do you want to put this in?")
Sheets(Y).Activate
End Sub

Thank you very much for the code, this is exactly what i was trying to
accomplish.
How about if you have a larger range to work with.
B1:B10
C1:C10
D1:D10
E1:E10
F1:F10
G1:G10
H1:H10
Thank you..