Copy values only

[Dr. Schwartz]

In Excel 2003 what do I need to change in this piece of code to only copy
values?

ThisWorkbook.Worksheets("source_sheet").Range(Cells(Jour_Rw, 1),
Cells(Jour_Rw, 18)).copy
Destination:=wb.Worksheets("target-sheet").Cells(FCDB_Rw, 1)

An easy one I guess, but still...

Thanks
The Doctor

 

[Ron de Bruin]

One way


Dim SourceRange As Range, DestRange As Range

Set SourceRange = ThisWorkbook.Worksheets("source_sheet").Range(Cells(Jour_Rw, 1), Cells(Jour_Rw, 18))

Set DestRange = wb.Worksheets("target-sheet").Cells(FCDB_Rw, 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

 

[Nigel]

You cannot use the copy:destination construct when you want to restrict the
copy use the following instead

ThisWorkbook.Worksheets("source_sheet").Range(Cells(Jour_Rw, 1),
Cells(Jour_Rw, 18)).Copy
wb.Worksheets("target-sheet").Cells(FCDB_Rw, 1).PasteSpecial
Paste:=xlPasteValues
Application.CutCopyMode = False

 

[Dave Peterson]

Watch your unqualified ranges:

Set SourceRange = ThisWorkbook.Worksheets("source_sheet") _
.Range(Cells(Jour_Rw, 1), Cells(Jour_Rw, 18))

I'd use:
with thisworkbook.worksheets("source_sheet")
Set SourceRange = .Range(.Cells(Jour_Rw, 1), .Cells(Jour_Rw, 18))
End with

(a couple of extra dots.)

 

[Dave Peterson]

Oops. I see you copied|pasted from the OP.

One way

Dim SourceRange As Range, DestRange As Range

Set SourceRange = ThisWorkbook.Worksheets("source_sheet").Range(Cells(Jour_Rw, 1), Cells(Jour_Rw, 18))

Set DestRange = wb.Worksheets("target-sheet").Cells(FCDB_Rw, 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

 

[Ron de Bruin]

Yes

Good catch Dave