Macro - providing user with choices

[gossv]

Hi,

I would like to move or copy a worksheet into a new book and give the user a choice on which book to move it into and what to rename the worksheet.

This is my macro and the "......." parts is where I would like it to allow the suer to choose.

Can someone please help me.

Thank you


Sheets("BC Report").Select
Sheets("BC Report").Copy Before:=Workbooks("..............."). _
Sheets(1)
Range("A1:G48").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("BC Report").Select
Range("A1").Select
Sheets("BC Report").Select
Sheets("BC Report").Name = "............."
Range("A1").Select

 

[Richard Schollar]

Hi

Do you want the choice to just be from currently open workbooks, or would you like the user to have a choice of closed ones too (ie ones currently sat on a netwrok drive for instance)?


EDIT: a very simple option would be to get your users to type in the workbook name into an InputBox, but I was thinking more along the lines of selecting the file itself or the name of an open file from a list. This does increase the complexity somewhat, however.

An input box could be as simple as:

Dim strWB as String, wb As Workbook
strWB = InputBox("Enter Workbook Name")
If strWB<>"" Then
  On Error Resume Next
  Set wb = Workbooks(strWB)
  On Error Goto 0
  If Not wb Is Nothing Then _
    Sheets("BC Report").Copy Before:=wb.Sheets(1)
End If