I would like to create a macro that looks at cell B18, if there is data in
that cell, then copy that data to range A18:A68, then skip 52 rows and look
at cell B70, if there is data in that cell, then copy that data to range
B70:B120, then skip 52 rows and repeat until there is no data in the cell.

Can someone assist with that code? I have recorded a macro to accomplish
this in the past but now the spreadsheet I have doesn't have a set number of
rows anymore. I don't think that recording it will assure that I will always
pick up all data if rows are added.

Thanks again for your help.

HJ

try the following code (need some modification):

Sub CopyPasteData()
Dim rngSource As Range, rngDestination As Range
Dim rngAll As Range, rr As Integer, MaxRow As Integer

Set rngAll = ActiveSheet.UsedRange
MaxRow = rngAll.Rows.Count + rngAll(1, 1).Row
Application.ScreenUpdating = False
Set rngSource = Range("A18")
rr = rngSource.Row
While rr <= MaxRow
If rngSource < "" Then
Set rngDestination = Range(rngSource, rngSource.Offset(50, 0))
rngSource.Copy Destination:=rngDestination
Set rngSource = rngSource.Offset(52, 0)
rr = rngSource.Row
End If
Wend
Application.ScreenUpdating = True
End Sub

===== * ===== * ===== * =====
Daniel CHEN


www.Geocities.com/UDQServices
Free Data Processing Add-in<
===== * ===== * ===== * =====

"HJ" wrote in message
...
I would like to create a macro that looks at cell B18, if there is data in
that cell, then copy that data to range A18:A68, then skip 52 rows and
look
at cell B70, if there is data in that cell, then copy that data to range
B70:B120, then skip 52 rows and repeat until there is no data in the cell.

Can someone assist with that code? I have recorded a macro to accomplish
this in the past but now the spreadsheet I have doesn't have a set number
of
rows anymore. I don't think that recording it will assure that I will
always
pick up all data if rows are added.

Thanks again for your help.

HJ

Try the following Macro

Sub Test2()
Dim i%
i = 1
With Sheet1
Do While .Cells(18 + 52 * (i - 1), 2).Value < ""
With .Range(.Cells(18 + 52 * (i - 1), 1), .Cells(18 + 52 * (i)
- 1, 1))
.Value = Sheet1.Cells(18 + 52 * (i - 1), 2).Value
End With
i = i + 1
Loop
End With
End Sub


Alok Joshi

"HJ" wrote:

I would like to create a macro that looks at cell B18, if there is data in
that cell, then copy that data to range A18:A68, then skip 52 rows and look
at cell B70, if there is data in that cell, then copy that data to range
B70:B120, then skip 52 rows and repeat until there is no data in the cell.

Can someone assist with that code? I have recorded a macro to accomplish
this in the past but now the spreadsheet I have doesn't have a set number of
rows anymore. I don't think that recording it will assure that I will always
pick up all data if rows are added.

Thanks again for your help.

HJ

I can't seem to get this to work. When I ran it the first time it was
copying the wrong information. My computer froze so I needed to restart and
I can't get it to work again to tell you which information it was copying.

"Alok" wrote:

Try the following Macro

Sub Test2()
Dim i%
i = 1
With Sheet1
Do While .Cells(18 + 52 * (i - 1), 2).Value < ""
With .Range(.Cells(18 + 52 * (i - 1), 1), .Cells(18 + 52 * (i)
- 1, 1))
.Value = Sheet1.Cells(18 + 52 * (i - 1), 2).Value
End With
i = i + 1
Loop
End With
End Sub


Alok Joshi

"HJ" wrote:

I would like to create a macro that looks at cell B18, if there is data in
that cell, then copy that data to range A18:A68, then skip 52 rows and look
at cell B70, if there is data in that cell, then copy that data to range
B70:B120, then skip 52 rows and repeat until there is no data in the cell.

Can someone assist with that code? I have recorded a macro to accomplish
this in the past but now the spreadsheet I have doesn't have a set number of
rows anymore. I don't think that recording it will assure that I will always
pick up all data if rows are added.

Thanks again for your help.

HJ

for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,1).Resize(50).Value _
= cells(i,2).value
end if
Next


Your example had the destination moving from left to right by column. I
assumed that was a mistake and you wanted them in column A. If not

j = 1
for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,j).Resize(50).Value _
= cells(i,2).value
j = j + 1
if j 256 then
msgbox "Out of columns"
exit sub
end if
end if
Next

--
Regards,
Tom Ogilvy


"HJ" wrote in message
...
I can't seem to get this to work. When I ran it the first time it was
copying the wrong information. My computer froze so I needed to restart
and
I can't get it to work again to tell you which information it was copying.

"Alok" wrote:

Try the following Macro

Sub Test2()
Dim i%
i = 1
With Sheet1
Do While .Cells(18 + 52 * (i - 1), 2).Value < ""
With .Range(.Cells(18 + 52 * (i - 1), 1), .Cells(18 + 52 *
(i)
- 1, 1))
.Value = Sheet1.Cells(18 + 52 * (i - 1), 2).Value
End With
i = i + 1
Loop
End With
End Sub


Alok Joshi

"HJ" wrote:

I would like to create a macro that looks at cell B18, if there is
data in
that cell, then copy that data to range A18:A68, then skip 52 rows and
look
at cell B70, if there is data in that cell, then copy that data to
range
B70:B120, then skip 52 rows and repeat until there is no data in the
cell.

Can someone assist with that code? I have recorded a macro to
accomplish
this in the past but now the spreadsheet I have doesn't have a set
number of
rows anymore. I don't think that recording it will assure that I will
always
pick up all data if rows are added.

Thanks again for your help.

HJ

Thanks Tom. I tried your code and am getting an end if without block if error.

"Tom Ogilvy" wrote:

for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,1).Resize(50).Value _
= cells(i,2).value
end if
Next


Your example had the destination moving from left to right by column. I
assumed that was a mistake and you wanted them in column A. If not

j = 1
for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,j).Resize(50).Value _
= cells(i,2).value
j = j + 1
if j 256 then
msgbox "Out of columns"
exit sub
end if
end if
Next

--
Regards,
Tom Ogilvy


"HJ" wrote in message
...
I can't seem to get this to work. When I ran it the first time it was
copying the wrong information. My computer froze so I needed to restart
and
I can't get it to work again to tell you which information it was copying.

"Alok" wrote:

Try the following Macro

Sub Test2()
Dim i%
i = 1
With Sheet1
Do While .Cells(18 + 52 * (i - 1), 2).Value < ""
With .Range(.Cells(18 + 52 * (i - 1), 1), .Cells(18 + 52 *
(i)
- 1, 1))
.Value = Sheet1.Cells(18 + 52 * (i - 1), 2).Value
End With
i = i + 1
Loop
End With
End Sub


Alok Joshi

"HJ" wrote:

I would like to create a macro that looks at cell B18, if there is
data in
that cell, then copy that data to range A18:A68, then skip 52 rows and
look
at cell B70, if there is data in that cell, then copy that data to
range
B70:B120, then skip 52 rows and repeat until there is no data in the
cell.

Can someone assist with that code? I have recorded a macro to
accomplish
this in the past but now the spreadsheet I have doesn't have a set
number of
rows anymore. I don't think that recording it will assure that I will
always
pick up all data if rows are added.

Thanks again for your help.

HJ

My bad

for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,1).Resize(50).Value _
= cells(i,2).value
Next

--
Regards,
Tom Ogilvy



"HJ" wrote in message
...
Thanks Tom. I tried your code and am getting an end if without block if
error.

"Tom Ogilvy" wrote:

for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,1).Resize(50).Value _
= cells(i,2).value
end if
Next


Your example had the destination moving from left to right by column. I
assumed that was a mistake and you wanted them in column A. If not

j = 1
for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,j).Resize(50).Value _
= cells(i,2).value
j = j + 1
if j 256 then
msgbox "Out of columns"
exit sub
end if
end if
Next

--
Regards,
Tom Ogilvy


"HJ" wrote in message
...
I can't seem to get this to work. When I ran it the first time it was
copying the wrong information. My computer froze so I needed to
restart
and
I can't get it to work again to tell you which information it was
copying.

"Alok" wrote:

Try the following Macro

Sub Test2()
Dim i%
i = 1
With Sheet1
Do While .Cells(18 + 52 * (i - 1), 2).Value < ""
With .Range(.Cells(18 + 52 * (i - 1), 1), .Cells(18 +
52 *
(i)
- 1, 1))
.Value = Sheet1.Cells(18 + 52 * (i - 1), 2).Value
End With
i = i + 1
Loop
End With
End Sub


Alok Joshi

"HJ" wrote:

I would like to create a macro that looks at cell B18, if there is
data in
that cell, then copy that data to range A18:A68, then skip 52 rows
and
look
at cell B70, if there is data in that cell, then copy that data to
range
B70:B120, then skip 52 rows and repeat until there is no data in
the
cell.

Can someone assist with that code? I have recorded a macro to
accomplish
this in the past but now the spreadsheet I have doesn't have a set
number of
rows anymore. I don't think that recording it will assure that I
will
always
pick up all data if rows are added.

Thanks again for your help.

HJ

Thanks, it works like a charm.

Can you take a look at the post - Macro Revision needed from yesterday? You
and Ron de Bruin had helped me with the original macro that I am trying to
modify.

"Tom Ogilvy" wrote:

My bad

for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,1).Resize(50).Value _
= cells(i,2).value
Next

--
Regards,
Tom Ogilvy



"HJ" wrote in message
...
Thanks Tom. I tried your code and am getting an end if without block if
error.

"Tom Ogilvy" wrote:

for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,1).Resize(50).Value _
= cells(i,2).value
end if
Next


Your example had the destination moving from left to right by column. I
assumed that was a mistake and you wanted them in column A. If not

j = 1
for i = 18 to 65536 step 52
if cells(i,2).Value = "" then exit sub
cells(i,j).Resize(50).Value _
= cells(i,2).value
j = j + 1
if j 256 then
msgbox "Out of columns"
exit sub
end if
end if
Next

--
Regards,
Tom Ogilvy


"HJ" wrote in message
...
I can't seem to get this to work. When I ran it the first time it was
copying the wrong information. My computer froze so I needed to
restart
and
I can't get it to work again to tell you which information it was
copying.

"Alok" wrote:

Try the following Macro

Sub Test2()
Dim i%
i = 1
With Sheet1
Do While .Cells(18 + 52 * (i - 1), 2).Value < ""
With .Range(.Cells(18 + 52 * (i - 1), 1), .Cells(18 +
52 *
(i)
- 1, 1))
.Value = Sheet1.Cells(18 + 52 * (i - 1), 2).Value
End With
i = i + 1
Loop
End With
End Sub


Alok Joshi

"HJ" wrote:

I would like to create a macro that looks at cell B18, if there is
data in
that cell, then copy that data to range A18:A68, then skip 52 rows
and
look
at cell B70, if there is data in that cell, then copy that data to
range
B70:B120, then skip 52 rows and repeat until there is no data in
the
cell.

Can someone assist with that code? I have recorded a macro to
accomplish
this in the past but now the spreadsheet I have doesn't have a set
number of
rows anymore. I don't think that recording it will assure that I
will
always
pick up all data if rows are added.

Thanks again for your help.

HJ