How to copy Excel records?
 

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

How to copy Excel records?
 

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" wrote in message
...
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

How to copy Excel records?
 

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.

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

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.

How to copy Excel records?
 

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).EntireRo w.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" wrote in message
...
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.

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

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.

How to copy Excel records?
 

Thank you very much. It works perfectly.

Best regards
abc

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).EntireRo w.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

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.

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

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.

How to copy Excel records?
 

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

Thank you very much. It works perfectly.

Best regards
abc

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).EntireRo w.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

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.

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

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.

How to copy Excel records?
 

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

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

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

"abc" wrote in message
...
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

Thank you very much. It works perfectly.

Best regards
abc

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).EntireRo w.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

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.

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

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.

How to copy Excel records?
 

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

Best regards
abc


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

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

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

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

Thank you very much. It works perfectly.

Best regards
abc

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).EntireRo w.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

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.

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

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.