Meatball
05-14-2009, 01:50 PM
Hi, I am trying to create a macro to to insert copied rows and search did have exactly what I am trying so I made this new thread.
I want to read the number in Col D. If it is more than 1, I need to copy the row and insert the copies just below the row in question. The total rows inserted would be 1 less than Col D. So if Row 8 Col D is 5 then copy row 8 and insert copied row into the 4 rows below, 9 thru 12. Then Col D needs to be changed to 1 in all 5 rows.
I also need Col K to be factored in such a way that I can turn the factoring on and off. In other words, in some rows Col K will equal 1. On the first run of the macro I do not want those rows expanded, but on the second run I do. The manor in which that is done is optional, second run of macro, message box to choose yes or no for those rows, 2 different macro's, etc.
I tried to start it but what I saw when searching makes me think I would never get this by myself.
Doing a basic start this is what I came up with. I did not yet try to factor in Col K. It may be useless but I won't learn if I don't do some of this stuff myself so I will compare it to the final result
Option Explicit
Sub QtyExtractor()
Dim i As Integer
Dim lr As Integer
Dim r As Row
lr = Range("A8:T" & Range("A2000").End(xlUp).Row)
i = Range("D")
For Each r In lr
If i > 1 Then
Rows.Copy
'Next Row insert copied cells i-1 rows
'make all column D to 1
Next r
End Sub
Thanks in advance for any help.
I want to read the number in Col D. If it is more than 1, I need to copy the row and insert the copies just below the row in question. The total rows inserted would be 1 less than Col D. So if Row 8 Col D is 5 then copy row 8 and insert copied row into the 4 rows below, 9 thru 12. Then Col D needs to be changed to 1 in all 5 rows.
I also need Col K to be factored in such a way that I can turn the factoring on and off. In other words, in some rows Col K will equal 1. On the first run of the macro I do not want those rows expanded, but on the second run I do. The manor in which that is done is optional, second run of macro, message box to choose yes or no for those rows, 2 different macro's, etc.
I tried to start it but what I saw when searching makes me think I would never get this by myself.
Doing a basic start this is what I came up with. I did not yet try to factor in Col K. It may be useless but I won't learn if I don't do some of this stuff myself so I will compare it to the final result
Option Explicit
Sub QtyExtractor()
Dim i As Integer
Dim lr As Integer
Dim r As Row
lr = Range("A8:T" & Range("A2000").End(xlUp).Row)
i = Range("D")
For Each r In lr
If i > 1 Then
Rows.Copy
'Next Row insert copied cells i-1 rows
'make all column D to 1
Next r
End Sub
Thanks in advance for any help.