Open user specified Excel workbook and copy data range to another workbook

Question

I am trying to run a macro in Excel where the user chooses a file to open and the macro will copy a range from that workbook and paste it to a user specified cell in the original workbook. My attempt at the code is shown below. I have "wbThis" as the original workbook to be pasted to and "wbTarget" as the workbook that is being opened and copied from. When I run the code, I can choose what file I want to open but it gives an error saying "Object required" and doesn't proceed further into copying and pasting.

Is there a way to use Workbook.Open instead of Application.GetOpenFilename but still have the user be able to choose what file to open?

Thank you for your help.

Dim wbTarget As Workbook
Dim wbThis As Workbook

Set wbThis = ActiveWorkbook
Set wbTarget = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open data")

wbThis.Activate
Set rDest = Application.InputBox(Prompt:="Please select the Cell to paste to", Title:="Paste to", Type:=8)
On Error GoTo 0
wbTarget.Activate
Application.CutCopyMode = False
wbTarget.Range("A1").Select
wbTarget.Range("B6:B121").Copy
wbThis.Activate
rDest.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

wbTarget.Close False

Daniel Möller Daniel Möller · Accepted Answer · 2013-03-26T16:04:17

GetOpenFileName does not really opens a file, just gets it's name. Try doing Application.Workbooks.Open(TheResultOfGetOpenFileName)

dim FileName as string
FileName = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open data")

Set wbTarget = Application.Workbooks.Open(FileName)

Open user specified Excel workbook and copy data range to another workbook

1 Answers