Need Help with Code for Copy and Pasting between Workbooks

rsrasc · #1 03-09-2016, 01:23 PM

Hi all,

I'm wondering if the below code can be re-written. I'm trying to copy and paste a range (multiple cells) from a workbook ("Text") to another workbook called "Template" but does not work for me and I can't figure it out.

Code:

Sub Macro5()
'
' Macro2 Macro
'

'
    Windows("Test.xls").Activate
    Range("A12:I12").Select
    Selection.Copy
    Windows("Template.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C6").Select
End Sub

The purpose of my question is that I would like to copy multiple cells with the above "re-written" code or a new code so I can copy multiple cells like I did with the below code. The code shown below worked for me when copying and pasting from one sheet to another sheet in the same workbook.

Code:

Sheets("Interface").Range("C6,C10,C14,C18,C22,C26,C30,C34,C38,C42,C46,C50").Copy Destination:=Sheets("MyData").Range("B6")

Any help is appreciated.

Thanks,
Cheers!

Debaser · #2 03-10-2016, 06:12 AM

Just specify the workbooks:

Code:

Workbooks("Test.xls").Sheets("Sheet1").Range("C6,C10,C14,C18,C22,C26,C30,C34,C38,C42,C46,C50").Copy Destination:=Workbooks("Template.xlsm").Sheets("Sheet1").Range("B6")

for example.

rsrasc · #3 03-10-2016, 07:57 AM

Hello Debaser,

Thank you so much for your response. I managed to get it to work with the following code:

Code:

Workbooks("PPE.xls").Sheets("Page 1").Range("A12").Copy Destination:=Workbooks("MyData.xlsm").Sheets("Sheet1").Range("A6")

but it does not work with the following code:

Code:

Workbooks("PPE.xls").Sheets("Page 1").Range("A12,A16,A20,A24, etc...").Copy Destination:=Workbooks("MyData.xlsm").Sheets("Sheet1").Range("A6")

With the above code is giving me the following error:

Run-time error '1004':

Application-defined or object-defined error

So, if you or anyone can help me with this, I will appreciate it. I'm attaching a copy of the source ("PPE") file for any help.

Thanks again,

Cheers!

Debaser · #4 03-10-2016, 08:18 AM

It's because you have merged cells (which are the bane of VBA). You need to use:

Code:

Workbooks("PPE.xls").Sheets("Page 1").Range("A12:I12,A16:I16,A20:I20,A24:I24").Copy Destination:=Workbooks("Book2").Sheets("Sheet1").Range("A6")

Note that the Range() property can only take up to 255 characters in the address string.

rsrasc · #5 03-10-2016, 09:09 AM

Damn merged cells... Got it finally.

Thank you so much for your time. I appreciate it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy + Pasting Excel Tables in word have 8pt spacing	Jiing Chiang	Word Tables	4	01-14-2016 05:43 PM
Copy and pasting problem Word 2010	Sarah262	Word	3	08-16-2012 03:46 PM
Loop through folder of workbooks and copy range to other workbook	Snvlsfoal	Excel Programming	3	07-29-2011 05:55 AM
copy-pasting hyperlinked images: work only at the edges	Xuanzang	PowerPoint	0	12-07-2010 02:35 AM
help Copy from excel and pasting as a metafile in powerpoint	Juiceapple	Office	1	09-15-2009 09:52 AM