macro to add rows and copy and paste

[Steve]

I ran this macro and it adds 5 rows to each one and does
copy and paste the correct information, but how do I have
it read the quanity in column 4 and add that many rows and
paste that many. Because my quantities are different on
different rows.

Thanks a bunch

Will this solution work if my quanitites are different for
each order, its not always a quantity of 5 that I need
rows for.

Thanks a bunch
Steve

 

[JC]

Oh, so you want it to enter the number of rows based on
the quantity? For instance, since the quantity is 5, you
need to insert 4 rows and copy that info into the rows.
That would require a rewrite as I thought you always
wanted it to be four inserts. Let me see what I can do.
JC

 

[Don Guillett]

See if something like this will help.to insert rows. Should be a LOT
quicker.

Sub insertXrows()
on error resume next
for i = 300 To 2 Step -1
x = Cells(i, "d")
Cells(i, "a").Resize(x, 1).EntireRow.Insert
Next i
End Sub

To insert rows and copy down
Sub insertXrows_copydn()
On Error Resume Next
For i = 16 To 2 Step -1
x = Cells(i, "d")
Y = Cells(i, "a")
Cells(i, "a").Resize(x, 1).EntireRow.Insert
Cells(i, "a").Resize(x, 1).Value = Y
Next i
End Sub

 

[STEVE]

the macro added the rows good except for one extra row per
sale. The copy didn't work at all. I appreciate the
effort, Any thoughts

Thanks
Steve

 

[Don Guillett]

What the second macro did was use col A info and insert the number of rows
specified in col D and copy col A info down.
In other words, if you have.. Tested

ColA ColB ColC ColD
a x x 1
b x x 2
c x x 3
d x x 4

Now you will have
ColA ColB ColC ColD
a
a x x 1
b
b
b x x 2
c
c
c
c x x 3
d
d
d
d
d x x 4

 

[Steve]

Don,

I see what it is doing, but I am trying to edit the macro
to copy all 4 columns down, and insert the number of rows
in column D -1 row, becuase if it inserts the number in d
it will be long by one row.

Much appreciated.

 

[Don Guillett]

This should do it for columns A-C. If you really want the number in col D
also then change to suit.
======
Sub insertXrows_copydn()
On Error Resume Next
For i = 16 To 2 Step -1
x = Cells(i, "d")
Y = Range(Cells(i, "a"), Cells(i, "c"))
Cells(i, "a").Resize(x - 1, 1).EntireRow.Insert
Cells(i, "a").Resize(x, 3).Value = Y
Next i
End Sub

 

[steve]

Thanks Don it works perfect, If igured out how to change
the row numbers and more columns to copy. This will save
a good amount of time. the one thing the macro doesn't do
though is if column D is a negative it skips it, however I
don't have that many credit in a month.

Thanks
Steve

 

[Don Guillett]

You would have to use another method that said if negative do this because
the macro is inserting rows based on col D. Sure am glad that it is not
subtracting rows.

 

[Don Guillett]

Wait a minute. I just thought of something. try it.
abs turns a negative into a positive so it should work.

x = abs(Cells(i, "d"))