0

I am trying to select some data from a workbook from Excel, copy it and paste it into another. But when I run my code in VBA it simply does nothing.

The code can be seen below:

Sub foo3()
Dim x As Workbook
Dim y As Workbook
Dim vals As Variant

'## Open both workbooks first:
Set x = Workbooks.Open("<Path of the origin\Archive.xslm>")
Set y = Workbooks.Open("<Destination Path\Archive1.xslm>")

'Store the value in a variable:
vals = x.Sheets("Plan1").Range("A1").Value

'Use the variable to assign a value to the other file/sheet:
y.Sheets("Plan1").Range("A1").Value = vals

'Close x:
x.Close
y.Close

End Sub

Could anyone please help me find the solution for it? Thank you!

3
  • Please help!! :( Aug 15, 2016 at 19:40
  • Which workbook has the VBA in it? Aug 16, 2016 at 1:20
  • It's must simpler than that. You don't even have to open the WB's. And you don't have to use VBa code either! Or do you prefer code?
    – ejbytes
    Aug 17, 2016 at 2:52

2 Answers 2

0

Replace Get rid of your code:

Set x = Workbooks.Open("")

Directly from the cell in your destination book that's open:

='C:\Users\youtheuser\Desktop\[bookC.xlsx]Sheet1'!$A$1


or Dynamic

='C:\Users\youtheuser\Desktop\[bookC.xlsx]Sheet1'!A1

That's all there is to it!

0

Excel has a built in macro recording capability that will generate VBA code based on your actions. Your best bet in debugging this issue is to record a macro of you 'manually' doing what it is you are trying to script in VBA and subsequently reviewing the code that gets generated.

Always make sure that the recorded macro actually does what you were doing - it's not 100% accurate.

If running the macro that it generate is doing what you wanted, you can review the code for it in the VBA editor in the workbook that you create the macro in. Note that it doesn't have to be one of the two workbooks of interest in your code excerpt, it can be another worksheet otherwise unrelated to them.

Based on how the autogenerated code is written you should be able to see the issue with your own code. If not, you can always just use that as-is (move it into your function instead of in the macro).

You can find any number of guides on recording macros. Here's a straightforward guide for excel 2013.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .