Range(sheet2) = Range(sheet1)

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. 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 where:

(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 where:

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

Thanks.

Howard
Click to expand...

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
 
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!
Click to expand...

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
Click to expand...

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.
 
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
Click to expand...

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.
 
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
Click to expand...

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.
 
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.
Click to expand...

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.
 
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:
Click to expand...

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