Copying values from one Workbook to Another
 

Hi,

I am trying to copy values from one workbook to another. I will be copying
many ranges.

The code below is my attempt at a simplified version. I got to the last
line where the vba code died.

Can someone please point out where I went wrong? Thank you.

Best regards,
Kevin


Sub GetData()

Dim wkbkSource As Workbook
Dim oSourceSheet As Object

Dim sSourceBook As String
Dim sSourceSheet As String

Dim oCurrentSheet As Object '\sets activesheet as oCurrentSheet

Set oCurrentSheet = ActiveSheet

sSourceBook = oCurrentSheet.Range("xsSWkBkName")
'\ "Source.xls" was successfully transferred to sSourceBook

sSourceSheet = oCurrentSheet.Range("xsSWkShtName")
'\ "SourceSheet" was successfully transferred to sSourceSheet

On Error Resume Next
Set wkbkSource = Workbooks(sSourceBook)
On Error GoTo 0
If wkbkSource Is Nothing Then
Set wkbkSource = Workbooks.Open(sSourceBook)
End If

Set oSourceSheet = wkbkSource.Worksheets(sSourceSheet)

'\ Step below fails
'\ I am trying to copy the values from the Source Sheet in the Source
Workbook
'\ to the Current Sheet.
'\ Run time error 438, Object doesn't support this property or method.

oCurrentSheet.Range("DestRange").Values =
oSourceSheet.Range("MySourceRange").Values


End Sub

Copying values from one Workbook to Another
 

The value property doesn't have an "s" on the end

oCurrentSheet.Range("DestRange").Values =
oSourceSheet.Range("MySourceRange").Values

should be

oCurrentSheet.Range("DestRange").Value =
oSourceSheet.Range("MySourceRange").Value

--
Regards,
Tom Ogilvy


"Kevin H. Stecyk" wrote in message
...
Hi,

I am trying to copy values from one workbook to another. I will be
copying
many ranges.

The code below is my attempt at a simplified version. I got to the last
line where the vba code died.

Can someone please point out where I went wrong? Thank you.

Best regards,
Kevin


Sub GetData()

Dim wkbkSource As Workbook
Dim oSourceSheet As Object

Dim sSourceBook As String
Dim sSourceSheet As String

Dim oCurrentSheet As Object '\sets activesheet as oCurrentSheet

Set oCurrentSheet = ActiveSheet

sSourceBook = oCurrentSheet.Range("xsSWkBkName")
'\ "Source.xls" was successfully transferred to sSourceBook

sSourceSheet = oCurrentSheet.Range("xsSWkShtName")
'\ "SourceSheet" was successfully transferred to sSourceSheet

On Error Resume Next
Set wkbkSource = Workbooks(sSourceBook)
On Error GoTo 0
If wkbkSource Is Nothing Then
Set wkbkSource = Workbooks.Open(sSourceBook)
End If

Set oSourceSheet = wkbkSource.Worksheets(sSourceSheet)

'\ Step below fails
'\ I am trying to copy the values from the Source Sheet in the Source
Workbook
'\ to the Current Sheet.
'\ Run time error 438, Object doesn't support this property or method.

oCurrentSheet.Range("DestRange").Values =
oSourceSheet.Range("MySourceRange").Values


End Sub

Copying values from one Workbook to Another
 

Hi Tom,

Lol. Thank you for pointing out my error Tom!

I had thought I erred with assigning the sheet object. But the error was a
simple "s".

Again, thank you!

Best regards,
Kevin


Tom Ogilvy wrote in message ...
The value property doesn't have an "s" on the end

oCurrentSheet.Range("DestRange").Values =
oSourceSheet.Range("MySourceRange").Values

should be

oCurrentSheet.Range("DestRange").Value =
oSourceSheet.Range("MySourceRange").Value