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


--

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


"Dr. Schwartz" <(E-Mail Removed)> wrote in message news:0B9A9B2D-DBD4-4A05-80DD-(E-Mail Removed)...
> 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

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

--

Regards,
Nigel
(E-Mail Removed)



"Dr. Schwartz" <(E-Mail Removed)> wrote in message
news:0B9A9B2D-DBD4-4A05-80DD-(E-Mail Removed)...
> 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

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(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
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
> "Dr. Schwartz" <(E-Mail Removed)> wrote in message news:0B9A9B2D-DBD4-4A05-80DD-(E-Mail Removed)...
> > 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

--

Dave Peterson

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(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
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
> "Dr. Schwartz" <(E-Mail Removed)> wrote in message news:0B9A9B2D-DBD4-4A05-80DD-(E-Mail Removed)...
> > 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

--

Dave Peterson

Yes

Good catch Dave



--

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


"Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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(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
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>> "Dr. Schwartz" <(E-Mail Removed)> wrote in message news:0B9A9B2D-DBD4-4A05-80DD-(E-Mail Removed)...
>> > 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
>
> --
>
> Dave Peterson