Copy values only

  • Thread starter Thread starter Dr. Schwartz
  • Start date Start date
D

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
 
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
 
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
 
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.)
 
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
 
Back
Top