How to copy Excel records?

[abc]

Hello

My Excel file has thousends of records and columns let's say
from A to Z.
In column Z there is one of numbers: 0 or 1 or 2 or 3.
The problem is how to copy each record 0 or 1 or 2 or 3 times
and insert the copies under the original.

I'd be very grateful for your help.

Regards
abc

 

[Otto Moehrbach]

Are you saying that if Column Z has, say a 3, that you want to copy that
row, say row 10, 3 times and place those 3 copied rows under row 10? That
would make rows 11, 12, and 13, the same as row 10. Is that what you want?
If so, you would need VBA. Post back and clarify what (and why??) you want
to do. HTH Otto

 

[abc]

Thanks for your reply.

The task is exactly as you described.
After copying the number in column Z won't be neccessary anymore,
so it needn't to be copied.
I need to do it because I use the Excel file in MailMerge process
and I don't know any other method to use a record more than once if needed.

 

[Otto Moehrbach]

The macro below will do that. I assumed that you have headers in row 1 and
your data starts in row 2. This macro loops through all the values in
Column Z and if the value is greater than zero, it inserts that many new
blank rows below that row, and copies the original row (25 columns) to all
the new rows. HTH Otto
Sub CopyRows()
Dim rColZ As Range
Dim c As Long
Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp))
For c = rColZ.Count To 1 Step -1
If rColZ(c) > 0 Then
rColZ(c).Offset(1).Resize(rColZ(c).Value).EntireRow.Insert
Cells(rColZ(c).Row, 1).Resize(, 25).Copy _
Cells(rColZ(c).Row + 1, 1).Resize(rColZ(c).Value)
End If
Next c
End Sub

 

[abc]

Thank you very much. It works perfectly.

Best regards
abc

 

[abc]

Hello again,

I have an additional question to you.
Where from could I learn to write such macros like yours?
I know that there is the help, but it is not a kind of tutorial
for beginners like me.

Best regards
abc

 

[Otto Moehrbach]

David McRitchie has a list of tutorials on his web site:

http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials

You could also peruse the Excel newsgroups, particularly the
Programming newsgroup. HTH Otto

 

[abc]

Thanks a lot. There are a lot of interesting things on the site.

Best regards
abc