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
klafert wrote:
[Quoted Text] >
>
> 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
|