Use mouse to select sheet

[Jeff Kelly]

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

 

[John_John]

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" έγγÏαψε:

 

[Dave Peterson]

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