Or use functions in the range A100:D101 (you can hide the two rows if you want)
in A100 =A1, in B100 =B1, in C100 =I20, in D100 =J20
And in row 101 your other 4 cells

You can use this macro then

Sub test()
Sheets("Sheet2").Range("A1:D2").Value = _
Sheets("Sheet1").Range("A100:D101").Value
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tom Ogilvy" wrote in message ...
Sub Tester3()
varr = Evaluate("{""A1"",""A1"";""B1"",""B1"";" & _
"""C1"",""I20"";""D1"",""J20"";" & _
"""A2"",""A23"";""B2"",""B23"";" & _
"""C2"",""I42"";""D2"",""J42""}")
For i = LBound(varr, 1) To UBound(varr, 1)
Worksheets("Sheet2").Range(varr(i, UBound(varr, 2))).Value = _
Worksheets("Sheet1").Range(varr(i, LBound(varr, 2))).Value
Next
End Sub

--
Regards,
Tom Ogilvy

"John" wrote in message
...
I have a list of values in cells that I want to paste to a new worksheet,
however these values are not on either the same Row or column, but I want
the 'output' worksheet to be in the format

A1 = Value1; B1 = Value2; C1 = Value3; D1 = Value4
A2 = Value5; B1 = Value6; C1 = Value7; D1 = Value8

The layout of the above values in the 'source' worksheet is as follows

A1 = Value1; B1 = Value2; I20 = Value3; J20 = Value4
A23 = Value5; B23 = Value6; I42 = Value7; J42 = Value8

As you can see there is a set gap in rows between each of my 'segments' of
data. Values to 'output' worksheet should be pastespecial values, as the
source are formulated.

Thanks