Code assistance needed

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

[email protected]
www.Geocities.com/UDQServices
>Free Data Processing Add-in<
===== * ===== * ===== * =====

"HJ" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>