Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros or the Fastest way to Autofill Rows
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros or the Fastest way to Autofill Rows
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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
(Unsuccessfully!) Unhiding Rows in Excel 2003 | Excel Worksheet Functions | |||
Expand/Collapse Rows | Excel Worksheet Functions | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
What is fastest for this? The Small VBA or many Worksheet Functions...? | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) |