Copying a value from one workbook to another

[Jamie Jackson]

Hi Folks,

I can create a macro that copies a value from one cell to another, but since
this will be part of a nested routine, I'd like to figure out the most
elegant way to perform this one elementary piece. I suspect that the whole
activate > select > copy > activate > select paste thing that a macro creates
isn't the most elegant way to handle this.

Let's say I want to copy the value of:

Workbook: SourceWorkbook.xls
Worksheet: "Source Data Sheet"
Value: value of "C3"

.... to ...

Workbook: "TargetWorkbook.xls"
Worksheet: "Target Data Sheet"
Target Cell: "A2"

What's an elegant, programmatic way to perform this inter-workbook value
assignment?

Thanks,
Jamie

 

[Dan]

SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy
TargetWorkbook.Sheets("Target Data Sheet").Range("A2")

all on the same line.

Dan

 

[Jamie Jackson]

Thanks for the quick reply, Dan! That looks elegant, alright.

I'm not sure how literally to take your snippet, so let me flesh it out a
bit more, for your validation. I'm a super-noob, so if you spot any problems
that you can call out, you'll save me hours of head-scratching.

' I've got to open both Workbooks first, correct?
sourcePathName = "c:\sourcefiles\SourceWorkbook.xls"
targetPathName = "c:\TargetWorkbook.xls"

Workbooks.Open Filename:=sourcePathName, ReadOnly:=True
Workbooks.Open Filename:=targetPathName

' Is this an appropriate way to get a handle on the workbooks?
Dim SourceWorkbook as Workbook
Dim TargetWorkbook as Workbook
Set SourceWorkbook = Workbooks(sourcePathName)
Set TargetWorkbook = Workbooks(targetPathName)

' Perform copy
SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy _
TargetWorkbook.Sheets("Target Data Sheet").Range("A2")

Would you mind looking that over, and giving feedback?

Thanks,
Jamie

 

[Dan]

One possible option would be as follows (you will need to have the source
workbook open for this to work...

Sub CopyEx()
Dim sourcePathName As String
Dim targetPathName As String
Dim SourceWorkbook As Workbook
Dim TargetWorkbook As Workbook

targetPathName = "c:\TargetWorkbook.xls"
Set SourceWorkbook = ActiveWorkbook
Set TargetWorkbook = Workbooks.Open(targetPathName)

' Perform copy
With TargetWorkbook
SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy
..Sheets("Target Data Sheet").Range("A2")
.Save
.Close
End With

End Sub