I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

Why not a macro to copy all the way downcome back and insert rows using a
loop step 20

--
Don Guillett
SalesAid Software

"klafert" wrote in message
...
I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one
blank
row and I will copy the same columns for the next 21 rows - blank row
again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have
one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that
is
clear enough for somebody to understand if not let me know.

10915 didn't have enough data to keep each group with 22 rows.

But this worked for me.

Option Explicit
Sub testme()

Dim myFormulaRng As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyPerGroup As Long

With Worksheets("sheet1")
Set myFormulaRng = .Range("H2:L2")
FirstRow = 2
LastRow = 10915
'or use the last filled in row in column A??
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

HowManyPerGroup = 23

For iRow = 2 To LastRow Step HowManyPerGroup
myFormulaRng.Copy _
Destination:=.Cells(iRow, "H").Resize(HowManyPerGroup - 1)
Next iRow
End With

End Sub


But I did have to go back to clean up the extra rows at the bottom of that last
group.

And the code expects that H2:L2 have the formulas in each cell.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson

And just add, I'd delete up the blank rows. Those blank rows really mess things
up when you're filtering, doing pivottables, creating charts, or sorting.

You may want to consider just doubling the row height to make it look nicer when
printed.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson

Will the 22 line is not really blank - I had somebody help me copy the rows -
(wished I had used a macro to autofill that sheet), but anyway they copied 23
rows instead of 22 and their is data that needs to line up. In other words I
have data in column a2-ag on 23 but line 23 is not needed and I have to line
the data for the next set on line 24 and 23 will end up getting deleted out.
The data I am copying is :
Payfield # Payfield acct
H2 = 1 I2=50101
h3 =2 I3=50101
h4=3 I4=50101
h5=22 I5=22100
h6=23 and so on until H23
and the same but different column heading for I2-L2 for 23 lines. Besides
the formula I am copying other data besides the formula in column K2-K23. I
will copy the formula in column K using the Fill down command. After the
macros runs then there will a line with #N/A. Then I can sort and delete
those lines. I am pulling data from another spreadsheet using the Index
command. The line I want to skip wil not have anything to pull and will have
the error that I can sort and delete.

"Don Guillett" wrote:

Why not a macro to copy all the way downcome back and insert rows using a
loop step 20

--
Don Guillett
SalesAid Software

"klafert" wrote in message
...
I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one
blank
row and I will copy the same columns for the next 21 rows - blank row
again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have
one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that
is
clear enough for somebody to understand if not let me know.

I should have said the 1st row A1.L1 is a header row - Starting with row a2 I
copy 22 rows, want to skip one row and copy 22 more rows and skip again etc.

"Dave Peterson" wrote:

And just add, I'd delete up the blank rows. Those blank rows really mess things
up when you're filtering, doing pivottables, creating charts, or sorting.

You may want to consider just doubling the row height to make it look nicer when
printed.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson

Oh and the last filled row of the spreadsheet is A10914-r10914. What you did
almost work but it copied all the same data in each row which is
understandable considering I said I was copying the same formula all the way
down, but skipping a linel - Sorry for being as clear as mud - Does this help
or change things until then I am copying and pasting, possible trying to
adjust the macro you wrote, if I can? Help!!

"klafert" wrote:

I should have said the 1st row A1.L1 is a header row - Starting with row a2 I
copy 22 rows, want to skip one row and copy 22 more rows and skip again etc.

"Dave Peterson" wrote:

And just add, I'd delete up the blank rows. Those blank rows really mess things
up when you're filtering, doing pivottables, creating charts, or sorting.

You may want to consider just doubling the row height to make it look nicer when
printed.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson

Actually, this line:

myFormulaRng.Copy _
Destination:=.Cells(iRow, "H").Resize(HowManyPerGroup - 1)

copied that first row (2) with had the formulas to 22 rows all at once.

If you have calculation set to manual, then try calculating.



klafert wrote:

Oh and the last filled row of the spreadsheet is A10914-r10914. What you did
almost work but it copied all the same data in each row which is
understandable considering I said I was copying the same formula all the way
down, but skipping a linel - Sorry for being as clear as mud - Does this help
or change things until then I am copying and pasting, possible trying to
adjust the macro you wrote, if I can? Help!!

"klafert" wrote:

I should have said the 1st row A1.L1 is a header row - Starting with row a2 I
copy 22 rows, want to skip one row and copy 22 more rows and skip again etc.

"Dave Peterson" wrote:

And just add, I'd delete up the blank rows. Those blank rows really mess things
up when you're filtering, doing pivottables, creating charts, or sorting.

You may want to consider just doubling the row height to make it look nicer when
printed.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson


--

Dave Peterson

I kind of understand but not clearly. But on the column k2 is the only one
with the same formula. H2-J2 has different data and L2 has different data.
M2-R2 has the heading only. Once I get everything copied and pulled from the
other spreadsheet. Then I will use vlookup and extract from another
spreadsheet, but that will come over clean. Isnt here an autofill macro - You
now how u use the Fill Down command or whatever works. I can send you my
spreadsheet if it will make it clearer. Virus Free have virust software as
good as it works - Norton. Thanks.

"Dave Peterson" wrote:

Actually, this line:

myFormulaRng.Copy _
Destination:=.Cells(iRow, "H").Resize(HowManyPerGroup - 1)

copied that first row (2) with had the formulas to 22 rows all at once.

If you have calculation set to manual, then try calculating.



klafert wrote:

Oh and the last filled row of the spreadsheet is A10914-r10914. What you did
almost work but it copied all the same data in each row which is
understandable considering I said I was copying the same formula all the way
down, but skipping a linel - Sorry for being as clear as mud - Does this help
or change things until then I am copying and pasting, possible trying to
adjust the macro you wrote, if I can? Help!!

"klafert" wrote:

I should have said the 1st row A1.L1 is a header row - Starting with row a2 I
copy 22 rows, want to skip one row and copy 22 more rows and skip again etc.

"Dave Peterson" wrote:

And just add, I'd delete up the blank rows. Those blank rows really mess things
up when you're filtering, doing pivottables, creating charts, or sorting.

You may want to consider just doubling the row height to make it look nicer when
printed.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson


--

Dave Peterson

I put different formulas in each cell in H2:L2 and ran the macro.

The formulas in each column adjusted when I copied them to the group of 22 rows.

If you put your formulas in H2:L2, then select H2:L2 and edit|copy.
Then select H2:H23 and do Edit|Paste, what happens?

I didn't touch M2:R2. It wasn't mentioned before.

klafert wrote:

I kind of understand but not clearly. But on the column k2 is the only one
with the same formula. H2-J2 has different data and L2 has different data.
M2-R2 has the heading only. Once I get everything copied and pulled from the
other spreadsheet. Then I will use vlookup and extract from another
spreadsheet, but that will come over clean. Isnt here an autofill macro - You
now how u use the Fill Down command or whatever works. I can send you my
spreadsheet if it will make it clearer. Virus Free have virust software as
good as it works - Norton. Thanks.

"Dave Peterson" wrote:

Actually, this line:

myFormulaRng.Copy _
Destination:=.Cells(iRow, "H").Resize(HowManyPerGroup - 1)

copied that first row (2) with had the formulas to 22 rows all at once.

If you have calculation set to manual, then try calculating.



klafert wrote:

Oh and the last filled row of the spreadsheet is A10914-r10914. What you did
almost work but it copied all the same data in each row which is
understandable considering I said I was copying the same formula all the way
down, but skipping a linel - Sorry for being as clear as mud - Does this help
or change things until then I am copying and pasting, possible trying to
adjust the macro you wrote, if I can? Help!!

"klafert" wrote:

I should have said the 1st row A1.L1 is a header row - Starting with row a2 I
copy 22 rows, want to skip one row and copy 22 more rows and skip again etc.

"Dave Peterson" wrote:

And just add, I'd delete up the blank rows. Those blank rows really mess things
up when you're filtering, doing pivottables, creating charts, or sorting.

You may want to consider just doubling the row height to make it look nicer when
printed.

klafert wrote:

I have a spreadsheet that has 10915 lines. I am copying the same formula
from row 2 - columns H2..L2 to the next 21 rows then there will be one blank
row and I will copy the same columns for the next 21 rows - blank row again
and so on.

Example:

I highlight row 2 (column H2-L2) Fill down H2:L2 to h23:L23 then I have one
blank row and then I would copy H2-L2 thru H23-l23 to h25-L46 - Hope that is
clear enough for somebody to understand if not let me know.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson