copy / paste selective rows

  • Thread starter Kenny Kendrena via OfficeKB.com
  • Start date
K

Kenny Kendrena via OfficeKB.com

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!
 
M

Max

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
 
K

Kenny Kendrena via 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?
 
M

Max

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="","",Sheet1
!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
 
Top