MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy values from worksheet to worksheet

Posted by Ben on August 24, 2000 11:04 PM

How do I copy values from one worksheet to another in the same workbook without selecting cells or activating worksheets?

Posted by Celia on August 25, 0100 1:31 AM

If you mean with VBA :-



Posted by Ben on August 25, 0100 6:34 PM

Thanks Celia. Inspired by your no-select method, I'm trying to get out of copy & paste but I can't get this to loop (it will copy once though every other cell in "H" & "J" have appropriate #'s).

Sub pasteto()
Set sortnum = Sheets("Sheet1").Range("H5")
Set pasterng = sortnum.Offset(0, -7).Range("A1:I2")
Set newrange = Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).Range("A1:I2")
Do Until sortnum.Value = 0
If (sortnum.Value < 2 Or sortnum.Value = 2) And (sortnum.Offset(0, 2).Value = 2 Or sortnum.Offset(0, 2).Value > 2) Then
newrange.Value = pasterng.Value
End If
Set sortnum = sortnum.Offset(2, 0)
End Sub

What am I missing?

Posted by Celia on August 25, 0100 7:08 PM

You need to re-set the newrange before looping to the next sortnum. Add this line before "Loop" :-

Set newrange = Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).Range("A1:I2")


Posted by Ben on August 25, 0100 7:24 PM

Thanks -- very fast