duplicate a row with a macro

[craig51]

Hello all,

In excel, I want to write a macro that will do the following.

Lets say I have a sheet with 300 rows

I want to copy the first row, and paste this 180times in a separate worksheet.

Once its done this, move to the second row, and repeat the above, with it
being pasted under what i just did above, until its at the end of all the
rows.

Anyone got a scooby on how to do this!

Cheers

 

[Mike H]

Hi,

This copes each row in Sheet 1 180 times into sheet 2. Alt +F11 to open Vb
editor. Right click 'This Workbook' and insert module and paste this in. Tap
F5 in VB editor to run it.

Sub copyit()
Dim MyRange As Range
Sheets("Sheet2").Select
lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In MyRange
c.EntireRow.Copy
lastrow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
For x = 0 + lastrow2 To 180 + lastrow2
Cells(x, 1).PasteSpecial
Next
Next
End Sub

Mike

 

[Joel]

Sub copy180()

Set oldsht = ActiveSheet
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Copy 180"
Set newsht = ActiveSheet

NumberofCopies = 180
OldRowCount = 1
NewRowCount = 1
With oldsht
Do While .Range("A" & OldRowCount) <> ""
.Rows(OldRowCount).Copy _
Destination:=newsht.Rows(NewRowCount & ":" & _
(NewRowCount + NumberofCopies - 1))
NewRowCount = NewRowCount + NumberofCopies
OldRowCount = OldRowCount + 1
Loop
End With

End Sub

 

[craig51]

Thanking you both, much much appriecated!!!