Copy values only
 

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

ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

Copy values only
 

One way


Dim SourceRange As Range, DestRange As Range

Set SourceRange = ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dr. Schwartz" wrote in message ...
In Excel 2003 what do I need to change in this piece of code to only copy
values?

ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

Copy values only
 

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

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

--

Regards,
Nigel




"Dr. Schwartz" wrote in message
...
In Excel 2003 what do I need to change in this piece of code to only copy
values?

ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

Copy values only
 

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


Ron de Bruin wrote:

One way

Dim SourceRange As Range, DestRange As Range

Set SourceRange = ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Dr. Schwartz" wrote in message ...
In Excel 2003 what do I need to change in this piece of code to only copy
values?

ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

--

Dave Peterson

Copy values only
 

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

Ron de Bruin wrote:

One way

Dim SourceRange As Range, DestRange As Range

Set SourceRange = ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Dr. Schwartz" wrote in message ...
In Excel 2003 what do I need to change in this piece of code to only copy
values?

ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

--

Dave Peterson

Copy values only
 

Yes

Good catch Dave



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dave Peterson" wrote in message ...
Oops. I see you copied|pasted from the OP.

Ron de Bruin wrote:

One way

Dim SourceRange As Range, DestRange As Range

Set SourceRange = ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Dr. Schwartz" wrote in message ...
In Excel 2003 what do I need to change in this piece of code to only copy
values?

ThisWorkbook.Worksheets("source_sheet").Range(Cell s(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

--

Dave Peterson