Excel VBA - Data From One Workbook to Another

Question

I'm probably missing something simple here. It just isn't copying the data from the source workbook to the target workbook and no errors are tripping. The source workbook is opening just fine. The target workbook contains this code. Both workbooks contain a sheet called 'Data'. Any help would be greatly appreciated.

Sub TransferData()

   Dim wbTarget As Workbook
   Dim wbSource As Workbook

   Set wbSource = Workbooks.Open("C:\folder\source.xls")
   Set wbTarget = Workbooks.Open("C:\folder\target.xlsm")

   wbSource.Activate
   Sheets("Data").Select
   ActiveSheet.Range("B7").Copy

   wbTarget.Activate
   Sheets("Data").Select
   ActiveSheet.Range("A1").Paste

End Sub

See my edit - you need to specify which workbook you're copying from. Sheets("Data").Select is ambiguous because you have two workbooks with the sheet data open. Add wbSource and wbTarget as shown in my answer. — user1274820
– user1274820, Commented Jul 8, 2015 at 15:09
@Mark ?!?!?? wbTarget.Activate; Sheets("Data").Range("A1").Select; ActiveSheet.Paste; now the code run correctly, whatever the best solution is to user1274820 — Fabrizio
– Fabrizio, Commented Jul 8, 2015 at 15:11
@Fabrizio Nevermind. I thought you were trying to replace the whole chunk with one line (no activates or selects) which is a much better option. Didn't realize you were just modifying the last two lines. — Mark Balhoff
– Mark Balhoff, Commented Jul 8, 2015 at 15:17

user1274820 · Accepted Answer · 2015-07-08 15:07:30Z

2

I think your problem is here:

ActiveSheet.Range("B7").Copy

Excel doesn't know which workbook to copy from:

With wbSource
    .Activate
    .Sheets("Data").Select
    .ActiveSheet.Range("B7").Copy
End With

With wbTarget
    .Activate
    .Sheets("Data").Select
    .ActiveSheet.Range("A1").Paste
End With

Try this code without select statements.

Also, you said the code is in your target workbook?

If you're opening both workbooks, the code should be in a third, unrelated workbook.

Sub TransferData()

   Dim wbTarget As Workbook
   Dim wbSource As Workbook

   Set wbSource = Workbooks.Open("C:\folder\source.xls")
   Set wbTarget = Workbooks.Open("C:\folder\target.xlsm")

   wbSource.Sheets("Data").Range("B7").Copy wbTarget.Sheets("Data").Range("A1")

   wbTarget.Close(True)
   wbSource.Close(True)

End Sub

Here is code I use on a daily basis to copy a spreadsheet from one workbook to another:

Dim TWB As Workbook
Dim CopyWB As Workbook
Set TWB = ThisWorkbook
Set CopyWB = Workbooks.Open(FName, ReadOnly:=True)
CopyWB.Sheets(TWB.Sheets("Menu").ComboBox1.Text).Cells.Copy TWB.Sheets("DataSheet").Cells
CopyWB.Close (False)

edited Jul 8, 2015 at 15:07

answered Jul 8, 2015 at 15:01

user1274820

8,1894 gold badges39 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Thank you, User1274820! Perfect!

Richard · Accepted Answer · 2015-07-08 15:10:37Z

0

Can't you store the value instead of copy paste

Sub TransferData()
       Dim wbTarget As Workbook
       Dim wbSource As Workbook

       Set wbTarget = Application.ActiveWorkbook
       Set wbSource = Workbooks.Open("C:\folder\source.xls")


       wbSource.Activate
       Sheets("Data").Select
       xValue = ActiveSheet.Range("B7").Value

       wbTarget.Activate
       Sheets("Data").Select
       ActiveSheet.Range("A1").Value = xValue
End Sub

edited Jul 8, 2015 at 15:10

answered Jul 8, 2015 at 15:00

Richard

1052 silver badges11 bronze badges

2 Comments

David Over a year ago

Just tried this to no avail. Basically acting the same way.

Mark Balhoff Over a year ago

This should be equivalent to the simpler wbTarget.Sheets("Data").Range("A1").Value = wbSource.Sheets("Data").Range("B7").Value.

Collectives™ on Stack Overflow

Excel VBA - Data From One Workbook to Another

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related