You changed an "l" (el) to a 1 (one).

one of the Cells did not have the dot - so it only worked correctly if
recipes was the active sheet. Not sure if that was my omission or it got
plucked off by the mail program, but here is a corrected version.

Sub Tester9()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("I28"), .Range("J28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy Destination:= _
Worksheets("Sheet1") _
.Cells(k, l)
k = k + 1
j = j + 22
Loop
Next
End With
End Sub


If you want I:K then do this

Set rng = Union(.Range("A9"), .Range("B9"), _
.Range("I28"), .Range("J28"), .Range("K28"))


--
Regards,
Tom Ogilvy


"John" wrote in message
...
Scrub my last post, I took your latest code and substituted with cell Refs
as follows.........

Sub Tester9()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("I28"), .Range("J28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = 1 + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
Cells(j, cell.Column).Copy Destination:=Worksheets("Sheet1") _
.Cells(k, l)
k = k + 1
j = j + 22
Loop
Next
End With

End Sub

It only works correct for Column J, no other values are pasted. There is
blank cells in A10:B30, so the next data that I need that follows A9:B9 is
in A31:B31

Also don't need anything in C*:H* (although there are values in it). Then
I
need I28:K28, then following that the next values (for those columns) are
in
I50:K50

Thanks for your time






"Tom Ogilvy" wrote in message
...
Let's see, I guess you didn't say the cells in between were empty or
that
they started in row 1. Try this instead:

Sub Tester9()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("SheetSource")
Set rng = Union(.Range("A1"), .Range("B1"), .Range("I20"),
..Range("J20"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
Cells(j, cell.Column).Copy Destination:=Worksheets("SheetDest") _
.Cells(k, l)
k = k + 1
j = j + 22
Loop
Next
End With


--
Regards,
Tom Ogilvy



"John" wrote in message
...
Thanks Tom for your reply

Problem with this code is that I would have to hard type all ranges
in -
I'm
likely to have approx 150 'ranges' which I will have to paste

Is it possible to specify a 'Start' cell in each column and then Jump
down
22 cells to the next 'source cell, continue this in the same column
until
there are no values left,then move to Column B and do the same etc?




"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