Need help with simple macro
 

Hello All,

I do not work with excel on a daily basis, I'm actually a web developer
but I have a request to modify an excel spreadsheet via a macro.

Basically I have about 20 rows. One cell in each rows has a number
,i.e. A2 is 250
A3 is 400, etc.

I need to write a macro to take that number in the A cell and copy the
row that it exists in that many times.

So if Cell A2 has a value of 250, I need to copy Row 2 250 times.

I'm pretty sure I could figure this out if I scour the web and use
trial and error, but I was wondering if someone could help me out. I
dont think the macro would be terribly difficult.

Thanks in advance, :confused:

Garlnnd


--
garlnnd
------------------------------------------------------------------------
garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832
View this thread: http://www.excelforum.com/showthread...hreadid=473426

Need help with simple macro
 

Sub DupCells()
Dim cell as Range
for each cell in Worksheets(1).Range("A2:A21")
cell.EntireRow.Copy Destination:=worksheets(2) _
cells(rows.count,1).End(xlup)(2).Resize(cell.Value )
Next
End sub

Put you date in the first sheet in the tab order. It will write the copies
to the second sheet in the tab order, so make sure that sheet is blank.

Adjust the A2:A21 to reflect the cells on the first sheet that contain the
quantities.

--
Regards,
Tom Ogilvy



"garlnnd" wrote in
message ...

Hello All,

I do not work with excel on a daily basis, I'm actually a web developer
but I have a request to modify an excel spreadsheet via a macro.

Basically I have about 20 rows. One cell in each rows has a number
,i.e. A2 is 250
A3 is 400, etc.

I need to write a macro to take that number in the A cell and copy the
row that it exists in that many times.

So if Cell A2 has a value of 250, I need to copy Row 2 250 times.

I'm pretty sure I could figure this out if I scour the web and use
trial and error, but I was wondering if someone could help me out. I
dont think the macro would be terribly difficult.

Thanks in advance, :confused:

Garlnnd


--
garlnnd
------------------------------------------------------------------------
garlnnd's Profile:
http://www.excelforum.com/member.php...o&userid=27832
View this thread: http://www.excelforum.com/showthread...hreadid=473426

Need help with simple macro
 

garlnnd,

Sub TryNow()
Dim myRow As Long
For myRow = Range("A65536").End(xlUp).Row To 1 Step -1
If IsNumeric(Cells(myRow, 1).Value) Then
Cells(myRow, 1).EntireRow.Copy
Cells(myRow, 1).Resize(Cells(myRow, 1).Value).EntireRow.Insert
End If
Next myRow
End Sub

This will result in you having 251 rows of the same, if the number is 250 (the original + 250
copies). If you want to end up with 250 rows total, then use

Sub TryNow2()
Dim myRow As Long
For myRow = Range("A65536").End(xlUp).Row To 1 Step -1
If IsNumeric(Cells(myRow, 1).Value) Then
If Cells(myRow, 1).Value 1 Then
Cells(myRow, 1).EntireRow.Copy
Cells(myRow, 1).Resize(Cells(myRow, 1).Value - 1).EntireRow.Insert
End If
End If
Next myRow
End Sub

HTH,
Bernie
MS Excel MVP


"garlnnd" wrote in message
...

Hello All,

I do not work with excel on a daily basis, I'm actually a web developer
but I have a request to modify an excel spreadsheet via a macro.

Basically I have about 20 rows. One cell in each rows has a number
,i.e. A2 is 250
A3 is 400, etc.

I need to write a macro to take that number in the A cell and copy the
row that it exists in that many times.

So if Cell A2 has a value of 250, I need to copy Row 2 250 times.

I'm pretty sure I could figure this out if I scour the web and use
trial and error, but I was wondering if someone could help me out. I
dont think the macro would be terribly difficult.

Thanks in advance, :confused:

Garlnnd


--
garlnnd
------------------------------------------------------------------------
garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832
View this thread: http://www.excelforum.com/showthread...hreadid=473426

Need help with simple macro
 

Someone from another board nailed it with this:

Dim I As Long
Dim X As Long
Dim rng As Range

I = 2
Set rng = Range("A" & I)
While rng.Value < ""

X = rng.Value
rng.EntireRow.Copy

rng.Offset(1, 0).Resize(X - 1, 1).Insert Shift:=xlDown
I = I + X
Set rng = Range("A" & I)
Wend
Application.CutCopyMode = False

Thanks for your help.


--
garlnnd
------------------------------------------------------------------------
garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832
View this thread: http://www.excelforum.com/showthread...hreadid=473426

Need help with simple macro
 

garlnnd,

I wouldn't say "nailed it" since it blows up when the value in column A is 1 or is non-numeric.

Try my second version, which looks at both.

HTH,
Bernie
MS Excel MVP


"garlnnd" wrote in message
...

Someone from another board nailed it with this:

Dim I As Long
Dim X As Long
Dim rng As Range

I = 2
Set rng = Range("A" & I)
While rng.Value < ""

X = rng.Value
rng.EntireRow.Copy

rng.Offset(1, 0).Resize(X - 1, 1).Insert Shift:=xlDown
I = I + X
Set rng = Range("A" & I)
Wend
Application.CutCopyMode = False

Thanks for your help.


--
garlnnd
------------------------------------------------------------------------
garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832
View this thread: http://www.excelforum.com/showthread...hreadid=473426