Define array of sheets by inputbox

[Herman]

Hello
I'm editing a procedure to copy certain sheets TOGETHER from Workbook
A to Workbook B.
In book A the user will be asked to click his way to book B. I know
how to do that.
But with book B activated an inputbox should prompt for the sheets to
be copied together (selecting while holding the Ctrl key down). I
think this should be done by an array input (type 64) but I can't
figure it out.
Can anyone help me here?
Thank you very much.
Herman

 

[Tom Ogilvy]

The application.InputBox will allow selection of ranges, but it won't
support selecting sheets.

You can put up a userform with a listbox (set for multiselect) or separate
checkboxes with the sheets listed and let the user select from there.

 

[Dave Peterson]

Another option is to weasel and just have the user select the sheets first
before invoking your code:

Option Explicit
Sub testme01()

Dim resp As Long

If ActiveWindow.SelectedSheets.Count = 1 Then
resp = MsgBox(prompt:="You only have one sheet selected." _
& vbLf & "Do you want to continue?", _
Buttons:=vbYesNo)
If resp = vbNo Then
MsgBox "Please Group multiple sheets and try again"
Exit Sub
End If
End If

ActiveWindow.SelectedSheets.Copy _
before:=Workbooks("otherworkbook.xls").Worksheets(1)

End Sub