Instead of this which does the job but leaves the range on sheet 2 selected:

Sheets("Sheet1").Range("A2:F2").Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

I'm trying to do the code line whe

(Sheet2)Range = (sheet1)Range and does the offset & paste special also

Thanks.

Howard

Instead of this which does the job but leaves the range on sheet 2
selected:

Sheets("Sheet1").Range("A2:F2").Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial
Paste:=xlPasteValues

I'm trying to do the code line whe

(Sheet2)Range = (sheet1)Range and does the offset & paste special
also

Thanks.

Howard

No need for copy/paste when assigning values only. Both ranges need to
be the same size when assigning values...

rngTarget.Value = rngSource.Value

...where rngTarget is sized same as rngSource before assigning the
values...

With rngSource
rngTarget.Resize(.Rows.Count, .Columns.Count) = rngSource.Value
End With

...where rngTarget is 'Set' to the 1st cell position and the resize does
the rest!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, October 24, 2014 6:56:30 PM UTC-7, GS wrote:
Instead of this which does the job but leaves the range on sheet 2
selected:

Sheets("Sheet1").Range("A2:F2").Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial
Paste:=xlPasteValues

I'm trying to do the code line whe

(Sheet2)Range = (sheet1)Range and does the offset & paste special
also

Thanks.

Howard

No need for copy/paste when assigning values only. Both ranges need to
be the same size when assigning values...

rngTarget.Value = rngSource.Value

..where rngTarget is sized same as rngSource before assigning the
values...

With rngSource
rngTarget.Resize(.Rows.Count, .Columns.Count) = rngSource.Value
End With

..where rngTarget is 'Set' to the 1st cell position and the resize does
the rest!

--
Garry

Having trouble making it work.
See the comments in the code.

Also, am at a loss as to how to make it offset like this line:

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)

Howard

Sub PostMyInfo()
Application.ScreenUpdating = False

Dim rngTarget As Range
Dim rngSource As Range

'/ This line does indeed select the correct range("A2:F2") (used for test only)
Sheets("Sheet1").Range(Cells(2, 1), Cells(2, 6)).Select

'/ Using the Debug this line shows the first & last value of range("A2:F2")
Set rngSource = Sheets("Sheet1").Range(Cells(2, 1), Cells(2, 6))

With rngSource
'/ Using the Debug this line shows the first & last value of range("A2:F2")
'/ ERRORS on this line
Sheets("Sheet2").rngTarget.Resize(Cells(2, 1), Cells(2, 6)).Value = rngSource.Value
End With

Application.ScreenUpdating = True

End Sub

Hi Howard,

Am Sat, 25 Oct 2014 01:13:36 -0700 (PDT) schrieb L. Howard:

With rngSource
'/ Using the Debug this line shows the first & last value of range("A2:F2")
'/ ERRORS on this line
Sheets("Sheet2").rngTarget.Resize(Cells(2, 1), Cells(2, 6)).Value = rngSource.Value
End With

you did not set rngTarget.
Try:

Set rngTarget = Sheets("Sheet2").Range("A2")
With rngSource
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count) _
.Value = rngSource.Value
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Sat, 25 Oct 2014 10:21:44 +0200 schrieb Claus Busch:

Set rngTarget = Sheets("Sheet2").Range("A2")
With rngSource
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count) _
.Value = rngSource.Value
End With

rngSource is superfluous because of With rngSource:

Set rngTarget = Sheets("Sheet2").Range("A2")
With rngSource
rngTarget.Resize(.Rows.Count, .Columns.Count) _
.Value = .Value
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Sat, 25 Oct 2014 10:23:40 +0200 schrieb Claus Busch:

Set rngTarget = Sheets("Sheet2").Range("A2")
With rngSource
rngTarget.Resize(.Rows.Count, .Columns.Count) _
.Value = .Value
End With

and with the offset in rngTarget:

Sub PostMyInfo()
Application.ScreenUpdating = False

Dim rngTarget As Range
Dim rngSource As Range

Set rngSource = Sheets("Sheet1").Range(Cells(2, 1), Cells(2, 6))
Set rngTarget = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)

With rngSource
rngTarget.Resize(.Rows.Count, .Columns.Count).Value = rngSource.Value
End With

Application.ScreenUpdating = True
End Sub

modify the ranges to suit


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus has it but here's how I was preparing it when I read back...

Sub PostMyInfo()
Dim rngSource As Range, rngTarget As Range

Set rngSource = Sheets("Sheet1").Range(Cells(2, 1), Cells(2, 6))
Set rngTarget = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)

Application.ScreenUpdating = False
With rngTarget.Resize(Cells(2, 1), Cells(2, 6))
.Value = rngSource.Value
End With
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion