VBA copy and paste from random sheet into specific workbook

[oliviar]

Hi Guys,
I'm trying to get a macro to copy and paste columns A - E of whatever workbook I have open, open a specific workbook, and paste into that one.

I tried Copy, Open Workbook, Paste. But it loses the Copy in the process of opening the destination workbook.

Whats the way around this?

 

[Smitty]

It shouldn't:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br>    ActiveSheet.Cells.Copy<br>        Workbooks.Open Filename:="C:\Users\UserName\Desktop\Test.xls"<br>    ActiveSheet.Paste<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[oliviar]

Oh oh, I know where I am going wrong. I have to delete all the columns in the destination file.
THATS what kills the copy/paste right?

My code went:
Copy
Open
Delete
Paste (error!)

But if I go:
Copy
Open
Paste
Delete
... It will work?

 

[Smitty]

Yeah, if you delete, you'll clear the clipboard.

Pasting over information can be problematic if your pasted data is smaller than the original, so I generally open the destination worbook, do my thing there, then reactivate the source, copy, then paste.

You can set a reference to the source workbook in code before you open the other with something like:

wbname = ActiveWorkbook.Name

Then you can activate it from the other without having an explicit reference to it, since it'll be variable.

 

[oliviar]

Could you give me an example of using that kind of variable name for workbooks?

To be really basic, how would I do a macro that went from origin to destination, copied A1, back to origin and pasted it?

 

[Smitty]

OK, here's a quick example you should be able to modify:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> wb1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, wb2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>        <br>         <SPAN style="color:#007F00">'  Set a reference to the source wb</SPAN><br>            wb1 = ActiveWorkbook.Name<br>        <SPAN style="color:#007F00">'   Open the destination wb</SPAN><br>            Workbooks.Open Filename:="C:\Users\UserName\Desktop\Filename.xlsx"<br>        <SPAN style="color:#007F00">'   Set a reference to the destination wb</SPAN><br>            wb2 = ActiveWorkbook.Name<br>            <SPAN style="color:#007F00">'   Delete stuff here</SPAN><br>                <br>        <SPAN style="color:#007F00">'   Activate the source wb</SPAN><br>            Windows(wb1).Activate<br>        <SPAN style="color:#007F00">'   Copy the source data</SPAN><br>            ActiveSheet.Cells.Copy<br>        <SPAN style="color:#007F00">'   Activate the destination wb</SPAN><br>            Windows(wb2).Activate<br>        <SPAN style="color:#007F00">'   Paste from source</SPAN><br>            ActiveSheet.Paste<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[oliviar]

thanks so much,
I should be able to do heaps with this!