Macros or the Fastest way to Autofill Rows

[klafert]

I need a macros to autofill rows. I am working on one now but have a few
problems. I have one spreadsheet that has a row for each employee for each
payperiod. It has the employee name, check number, date, hours worked,
vacation sick, pay etc, earnings. The hours worked may be in 3 column
depending on if it is regular pay, overtime 1 pay, overtime 2 pay, and
commission - (which is an amont). I take that line and copy it in another
spreadsheet for 27 rows for one employee and one pay period. Then the I copy
the 2nd row which is another another employee and one pay period, etc etc. I
would like to design a macros so copy all the rows for a certain range, (say
column a1..j1). I have other data in columns k1..ac that will not get copy.
I am using an Index to pull that info, that is not a problem. I just need 27
rows for 1 row of data from the originaly spreadsheet.

 

[Dave Peterson]

So you end up with one sheet with 27 times the number of rows that you had
before?

If yes:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim HowManyCols As Long
Dim HowManyRows As Long

HowManyCols = 10
HowManyRows = 27

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets("sheet2")

With NewWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(DestCell.Value) Then
'stay put
Else
Set DestCell = DestCell.Offset(1, 0)
End If
End With

With CurWks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
.Cells(iRow, "A").Resize(1, HowManyCols).Copy _
Destination:=DestCell.Resize(HowManyRows, 1)
Set DestCell = DestCell.Offset(HowManyRows)
Next iRow
End With
End Sub