I am attempting to allow users to select a worksheet using the mouse and
then copy that sheet to a new sheet.
I am using this formula:-

Dim rng as Range
On Error Resume Next
set rng = Application.InputBox("use mouse to select any cell in desired
sheet", Type:=8)
On Error goto 0
if not rng is nothing then
msgbox "You selected the sheet named: " & rng.parent.name
else
msgbox "You hit cancel"
End If

Cells.Select
Selection.Copy

However when a sheet is selected, I am simply returned to the active sheet.

Help

Hi,

Try this

rng.Parent.Select
Cells.Select
Selection.Copy


Mike


On Sun, 17 May 2009 10:18:13 +0200, "Jeff Kelly"
wrote:

I am attempting to allow users to select a worksheet using the mouse and
then copy that sheet to a new sheet.
I am using this formula:-

Dim rng as Range
On Error Resume Next
set rng = Application.InputBox("use mouse to select any cell in desired
sheet", Type:=8)
On Error goto 0
if not rng is nothing then
msgbox "You selected the sheet named: " & rng.parent.name
else
msgbox "You hit cancel"
End If

Cells.Select
Selection.Copy

However when a sheet is selected, I am simply returned to the active sheet.

Help

Hi!

Try this way:

Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox("use mouse to select any cell in
desiredSheet ", Type:=8)
On Error GoTo 0

If Not rng Is Nothing Then
With rng.Parent
.Activate
MsgBox "You selected the sheet named: " & .Name
.Cells.Copy
End With
Else
MsgBox "You hit cancel"
End If




Ο χÏ�ήστης "Mike H" Î*γγÏ�αψε:

Hi,

Try this

rng.Parent.Select
Cells.Select
Selection.Copy


Mike


On Sun, 17 May 2009 10:18:13 +0200, "Jeff Kelly"
wrote:

I am attempting to allow users to select a worksheet using the mouse and
then copy that sheet to a new sheet.
I am using this formula:-

Dim rng as Range
On Error Resume Next
set rng = Application.InputBox("use mouse to select any cell in desired
sheet", Type:=8)
On Error goto 0
if not rng is nothing then
msgbox "You selected the sheet named: " & rng.parent.name
else
msgbox "You hit cancel"
End If

Cells.Select
Selection.Copy

However when a sheet is selected, I am simply returned to the active sheet.

Help

Probably better to check to see if that range was successful:

dim rng as rng

set rng = nothing
on error resume next
set rng = application.inputbox("use...", type:=8)
on error goto 0

if rng is nothing then
'exit sub '???
else
'do the work here
rng.parent.cells.copy
...
end if





Jeff Kelly wrote:

I am attempting to allow users to select a worksheet using the mouse and
then copy that sheet to a new sheet.
I am using this formula:-

Dim rng as Range
On Error Resume Next
set rng = Application.InputBox("use mouse to select any cell in desired
sheet", Type:=8)
On Error goto 0
if not rng is nothing then
msgbox "You selected the sheet named: " & rng.parent.name
else
msgbox "You hit cancel"
End If

Cells.Select
Selection.Copy

However when a sheet is selected, I am simply returned to the active sheet.

Help

--

Dave Peterson