Copy from One Workbook in Excel to Another Workbook

1
votes

I am ultimately going for something more complex, but I can't even get this simple version to work.

It will open the other files, but it just leaves the 2nd workbook (the one with the macro that stuff is supposed to be copied to) blank.

I have tried some suggestions from other "copy/paste between workbooks" threads and nothing is working.

Most give me a "paste method of worksheet class failed" error which I have no clue how to fix as even PasteSpecial doesn't fix it?

I am relatively new to VBA and need to get something running for my job. Thanks in advance for any help!

In my sample code below, I'm trying to copy from CPWholeDocTest1.xlsx to CPWholeDocTest2.xlsm.

Sub CopyPasteRawData()

Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open("C:\Users\rnewma\Documents\Excel Projects\Test\CPWholeDocTest1.xlsx")
Set y = Workbooks.Open("C:\Users\rnewma\Documents\Excel Projects\Test\CPWholeDocTest2.xlsm")

'Now, transfer values from x to y:
y.Sheets("Test").Range("A1:B5").Value = x.Sheets("Sheet1").Range("A1:B5")

'Close x:
'x.Close

End Sub
2
vbaexcelcopy-paste
Are you running this code from a macro enabled workbook or are you trying to run it from the .xlsx file ? – Vulthil
From the xlsm file, so it should be Macro Enabled. – Rich
Did you test R3uK's answer below, because I can't see anything wrong with the code. – Vulthil
Yes I did. It comes up completely blank. I even copy/pasted his modification over mine just to make sure I wasn't missing anything else and I just get nothing. – Rich
If you are running the code inside your .xlsm workbook, change Set y = Workbooks.Open(".....") to set y = ThisWorkbook. Did the trick for me. – Vulthil

2 Answers

0
votes

When transferring data from one range to another, you must add .Value or .Value2 to be explicit!
(.Value is the default property, so it is implicit when you don't specify it) :

Sub CopyPasteRawData()

Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open("C:\Users\rnewma\Documents\Excel Projects\Test\CPWholeDocTest1.xlsx")
Set y = Workbooks.Open("C:\Users\rnewma\Documents\Excel Projects\Test\CPWholeDocTest2.xlsm", ReadOnly:=False)
DoEvents
'Now, transfer values from x to y:
y.Sheets("Test").Range("A1:B5").Value = x.Sheets("Sheet1").Range("A1:B5").Value

'Close x:
'x.Close

End Sub
0
votes

I want to post this to help others as there are a ton of other threads that weren't quite right but close enough to confuse me. Hopefully this can help others in the future.

The Working Code:

Sub CopyPasteRawData2()

' CopyPasteRawData Macro
'
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open("pathname of file you are copying from")
Set y = ThisWorkbook

'Now, transfer values from x to y:
y.Sheets("sheet you are copying to").Range("A1:B5").Value = x.Sheets("sheet you are copying from").Range("A1:B5").Value

'Close x:
x.Close

End Sub