Excel VBA to copy specific worksheet to Active workbook

DougDodge
DougDodge used Ask the Experts™
on
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......
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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

frankhelk

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial