Using VBA to copy data from a closed workbook to the current, open workbook

[ftmthe3rd]

Like the title says, I'd like to copy data from a closed Excel workbook to the current one (which also houses the macro). I'm using Excel 2007.

The current workbook is named Carrier_Change.xlsx, and has one worksheet named "Analysis" where I'd like to copy the data to. It also has the macro that I'm running in it.

The closed file (which I'll refer to as the Report) also has one worksheet, but its location and name can vary. To find that Report, I've got a small function that prompts the user to select the closed report:

Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(1).Cells.Select
Selection.Copy

Beyond this, I'm stumped. I feel it must be trivial to do, but I can't figure out how to select the Carrier_Change workbook, paste the data in the "Analysis" worksheet, and close the Report workbook.

 

[xladept]

If you have no further use for the Report book, then, if you close it, the right workbook will be active - but try this code:

Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(1).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("Analysis").Activate
Cells(1, 1).Select: ActiveSheet.Paste

<table border="0" cellpadding="0" cellspacing="0" width="342"><colgroup><col width="342"></colgroup><tbody><tr height="17"> <td style="height:12.75pt;width:257pt" height="17" width="342">You can't do one thing. XLAdept</td> </tr></tbody></table>

 

[ftmthe3rd]

That works great, thank you.

The only issue is that when Excel closes the Report workbook, I get a popup regarding the large amount of data on the clipboard (which I obviously want to keep there). Is there another function I can add before I close that workbook that would automatically tell Excel to save all data to clipboard? If not, no big deal.

 

[xladept]

Hi FTMthe3rd,

Try:

Application.DisplayAlerts = False

But when I ran it, it had already pasted so I didn't need it anymore.

<table border="0" cellpadding="0" cellspacing="0" width="342"><colgroup><col width="342"></colgroup><tbody><tr height="17"> <td style="height:12.75pt;width:257pt" height="17" width="342">You can't do one thing. XLAdept</td> </tr></tbody></table>

 

[ftmthe3rd]

Perfect! Thanks again.

 

[buttonmasher]

Greetings all,

I looked up this thread and am having difficulty replicating this procedure in something similar that I'm trying to do. It is slightly modified as I need it to paste only the values from DDE data which is slow to populate. I am able to successfully get through the procedure, but then I get a Runtime error that the 'PasteSpecial method of Range class failed'. Tried a number of different things but I can't pinpoint exactly what I'm doing wrong.

Note: This is a subprocedure and both workbooks are open at the time of the code running as it starts out of the target workbook. The target workbook also always changes in filename when the date changes, which is why I haven't declared any variables for each of the workbooks in this subprocedure.

Range("A:U").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


Application.CutCopyMode = True
ActiveWorkbook.Close savechanges:=True


ThisWorkbook.Sheets("RDATA STATIC").Activate
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

 

[xladept]

Try copying closer to the pasting:

Range("A:U").Select Range(Selection, Selection.End(xlDown)).Copy

ThisWorkbook.Sheets("RDATA STATIC").Cells(1, 1).PasteSpecial Paste:=xlValues 

Application.CutCopyMode = False  
ActiveWorkbook.Close savechanges:=True

 

[p9j123]

This is an old post obviously but I find it really helpful on my current project, how about the other way around? the macro should copy the data from the open file to the close file?

If you have no further use for the Report book, then, if you close it, the right workbook will be active - but try this code:

Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(1).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("Analysis").Activate
Cells(1, 1).Select: ActiveSheet.Paste

You can't do one thing. XLAdept

<colgroup><col width="342"></colgroup><tbody>
</tbody>

 

[xladept]

I guess I don't understand your question?