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

Hi Claus,

With the offset as you posted the code works very well.

Thanks, I was indeed struggling with it.

Appreciate the help, Garry and Claus.

Regards,
Howard

hi Howard,

Am Sat, 25 Oct 2014 02:30:15 -0700 (PDT) schrieb L. Howard:

With the offset as you posted the code works very well.

if you want to run the macro from another sheet than sheet1 you have to
change setting rngSource:

With Sheets("Sheet1")
Set rngSource = .Range(.Cells(2, 1), .Cells(2, 6))
End With


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

Hi Claus,

Here is the entire code.
I have it in a Standard Module and it works well.

Do you notice any snags I should be aware of?

Howard


Sub PostSaleInfo()
Application.ScreenUpdating = False

Dim myCheck
Dim myCnt
Dim rngTarget As Range
Dim rngSource As Range

myCnt = Application.WorksheetFunction.CountA(Range("A2:F2" ))

If myCnt < 6 Then
MsgBox "You have only filled in " & myCnt & " cells in sales data."
Exit Sub
End If

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
Sheets("Sheet2").Range("G" & Rows.Count).End(xlUp)(2) = Date
End With

myCheck = MsgBox("Delete sales info?", vbYesNo)
If myCheck = vbNo Then
MsgBox "No Delete"
Exit Sub
Else
MsgBox "Yes Delete"
Sheets("Sheet1").Range("A2:F2").ClearContents
[A2].Activate
End If

Application.ScreenUpdating = True
End Sub

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

if you want to run the macro from another sheet than sheet1 you have
to
change setting rngSource:

Perhaps...

Sub PostMyInfo()
Dim rngSource As Range, rngTarget As Range
Const sMsg$ = "Select the range to copy values from"

Set rngSource = Application.InputBox(sMsg, Type:=8)
If rngSource Is Nothing Or Not _
WorksheetFunction.CountA(rngSource) = 6 Then Beep: Exit Sub

Set rngTarget = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)
With rngSource
rngTarget.Resize(.Rows.Count, .Columns.Count) = .Value
End With
End Sub

...where you can validate the existence/contents of rngSource and
proceed if all is good!

--
Garry

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

A good amount of good stuff.

Thanks guys.

Howard