copy / paste selective rows
 

Hi all,

I'm trying to copy a row of formulas and paste selectively (every 7th row)
the same formulas (keeping them relative to their row). FYI the 6 rows in
between consist of some #'s and some formulas. Thus, trying to use go to
special formulas doesn't do the trick. Is there a macro that lets me
copy and paste every x rows and loop until it reaches the end of my data?
Thanks!

--
Message posted via http://www.officekb.com

Perhaps something along these lines ..

Suppose you have in Sheet1

in C1: =SUM(A1:B1)
in C8: =SUM(A8:B8)
in C15: =SUM(A15:B15)
and so on

In Sheet2
--------
Put in C1:
=IF(MOD(ROWS($A$1:A1)-1,7)=0,SUM(A1:B1),"")
Copy C1 down to say, C15

In C1, C8, C15 will be the same relative formulas as in Sheet1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Kenny Kendrena via OfficeKB.com wrote in message
...
Hi all,

I'm trying to copy a row of formulas and paste selectively (every 7th row)
the same formulas (keeping them relative to their row). FYI the 6 rows in
between consist of some #'s and some formulas. Thus, trying to use go to

special formulas doesn't do the trick. Is there a macro that lets me
copy and paste every x rows and loop until it reaches the end of my data?
Thanks!

--
Message posted via http://www.officekb.com

Thank you, Max. I'm still having trouble, though.
What I have is data in d4:d6, and I want d7 to AVERAGE the #'s above. Then
I have header rows for each new person a couple blank lines and then the
same pattern of data/formulas all the way down (for instance, data in
d13:d15 that I want the average of in d16, and so forth).

Player Year Team W L

Abbott 04 KAN 3 5
03 KAN 1 2
02 SEA 1 3
avg. D7 E7



Player Year Team W L

Acevedo 04 CIN 5 3
03 CIN 2 0
02 CIN 4 2
avg. D16 E16

See what I mean?

--
Message posted via http://www.officekb.com

Assuming your data as posted is in Sheet1, starting from D4 down,
with the averages required in D7, D16, D25, etc
(i.e. every 9 cells from D7 onwards, average the 3 cells above)
and with corresponding averages across in col E, F, etc

In Sheet2
-----------
Put in D4:

=IF(MOD(ROWS($A$1:A1)-1,9)=3,AVERAGE(Sheet1!D1:D3),IF(Sheet1!D4="","",Sh eet1
!D4))

Copy D4 down and fill across as required
Example: copy down to D25, fill across to F25

The above will return what's in Sheet1's D4:F25,
but with the averages evaluated in:
D7:F7, D16:F16, D25:F25

Then, if desired, just select D4:F25
and do a copy paste special values
over D4:F25 in Sheet1 to overwrite
(but try this on a *spare copy* first)

Adapt to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kenny Kendrena via OfficeKB.com" wrote in message
...
Thank you, Max. I'm still having trouble, though.
What I have is data in d4:d6, and I want d7 to AVERAGE the #'s above.
Then
I have header rows for each new person a couple blank lines and then the
same pattern of data/formulas all the way down (for instance, data in
d13:d15 that I want the average of in d16, and so forth).

Player Year Team W L

Abbott 04 KAN 3 5
03 KAN 1 2
02 SEA 1 3
avg. D7 E7



Player Year Team W L

Acevedo 04 CIN 5 3
03 CIN 2 0
02 CIN 4 2
avg. D16 E16

See what I mean?

--
Message posted via http://www.officekb.com

Looks like this will do what I need it to do. Thank you, Max!

--
Message posted via http://www.officekb.com

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Kenny via OfficeKB.com wrote in message
...
Looks like this will do what I need it to do. Thank you, Max!

--
Message posted via http://www.officekb.com