Code assistance needed

[Guest]

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

 

[Daniel CHEN]

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

(e-mail address removed)
www.Geocities.com/UDQServices

Free Data Processing Add-in<

===== * ===== * ===== * =====

 

[Guest]

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

 

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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.