| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
