Excel VBA to copy specific worksheet to Active workbook

I am hoping to find code to copy cells of a workbook worksheet in sheet called "TASK", and paste into the active workbook. But ask the user which worksheet in the active workbook to copy to first.

So far the code I have uses the GetOpenFilename:

Sub FileDialogImport()

Dim strFileToOpen As String

strFileToOpen = Application.GetOpenFilename(Title:="Please Choose A File To Open For Import", FileFilter:="Excel Files *.xls* (*.xls*),")

If strFileToOpen = "" Then
MsgBox "No File Selected.", vbExclamation, "Sorry!"
Exit Sub
Else:
Workbooks.Open Filename:=strFileToOpen
End If

End Sub

And the following to select the used range once the file is open:

Sub SelectDateToCopy()

ActiveSheet.UsedRange.Copy

End Sub

It does not matter if the source file is opened or not, I just want to copy the used range on the worksheet "TASK"

The user should be asked which sheet to paste to for a destination.

Hope I explained this well enough......

Here is code (tested in Excel 2013) that will ask you for any cell on the destination worksheet. It will then ask you to choose a source workbook. It will then clear the destination worksheet, copy the used range from the source workbook, and paste special (values & formats) to the destination worksheet. The source workbook will then be closed.

If no source workbook is selected, a message will be displayed. If no destination cell is chosen, nothing further will happen. If the TASK worksheet doesn't exist, then nothing further will happen.

Sub FileDialogImport()
Dim strFileToOpen As String
Dim wsSource As Worksheet, wsDest As Worksheet
Dim wb As Workbook
Dim celHome As Range, celDest As Range
Set celHome = ActiveCell
On Error Resume Next
Set celDest = Application.InputBox("Please pick any cell on worksheet where you want imported data to be pasted.", Type:=8)
On Error GoTo 0

If Not celDest Is Nothing Then
    strFileToOpen = Application.GetOpenFilename(Title:="Please Choose A File To Open For Import", FileFilter:="Excel Files *.xls* (*.xls*),")
    If strFileToOpen = "" Then
        MsgBox "No File Selected.", vbExclamation, "Sorry!"
    Else
        Set wsDest = celDest.Worksheet
        On Error Resume Next
        Set wb = Workbooks.Open(Filename:=strFileToOpen)
        Set wsSource = wb.Worksheets("TASK")
        On Error GoTo 0
        
        If Not wsSource Is Nothing Then
            SelectDataToCopy wsSource, wsDest
            wb.Close SaveChanges:=False
        End If
        Application.Goto celHome
    End If
End If
End Sub

Sub SelectDataToCopy(wsSource As Worksheet, wsDest As Worksheet)
wsDest.Cells.Clear
wsSource.UsedRange.Copy
wsDest.Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
End Sub

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 82

byundtMechanical EngineerCommented: 2016-02-23

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 14

frankhelkCommented: 2016-03-22

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Excel VBA to copy specific worksheet to Active workbook

Who is Participating?