JLatham, thank you for taking so much time with this. Because of my lack of
knowledge in this 'in-depth' Excel programming, I did not realize the
complexity.
I am going to attempt to understand your Macro and put it in place. As to
your questions,

#1 The copy segment is all rows that have the value of (1) in col C,
specifically, E:R. S,T,U, , are blank and never have values and A,B,C,D are
useless to the calculation but the one describes a quantity of data that is
required to be place on sheet R1 at the cell U5.

#2 Hopefully I will not make that mistake again during my explanation. If
col C row 3 thru row 7 have a (1) in it, then the copy would be D3:R7

#3 The cells in col S on sheets R1, R2, R3, , , , do have borders but do not
have values in them on either sheets. My reasoning for ending on row R is in
case future needs require use of S,U,V, , , .

#4 I must keep the blank rows. The calculation requires five rows of each
value in col G no matter if the value is blank or there are five rows of
data. This contigency is based on five samples of data per each value in col
G with a value of (1), (2), , , in col C.

#5 The first point of entry on sheets R1, R2, , , is U5. This requirement
was changed on me this afternoon when the design requirements changed. If
you can point out where this entry point is described, it will help me a lot.

Thanks again for your time. I promise I will work on being more thorough
and precise in my descriptions and requirements. You can see I am getting
better I hope.

Shu

"JLatham" wrote:

For the most part your needs are well expressed, but you got me in a couple
of places.
#1 - for all values in column C is there to be a separate sheet to copy that
data to, i.e., sheet names like R1 (col C = 1), R2 (col C = 2), R3 (col
C=3)...R10 (col C = 10) ?? If this is the case, do all possible sheets
exist, or must they be created on the fly?

#2 - in the first paragraph you say then copy columns A-S, but not blank
entries past R ... But down below you say "The copy begins in column E and
goes to column R" -- so what is it we really need to copy? A-R + any
non-blanks after R, or E-R and non-blanks beyond R?

#3 - If the sheets to be copied over to (R1, R2 etc) don't already have data
in the potential pasting area (over beyond column U? which is about where
column R from original sheet would end up at), what harm in copying all cells
from A (or E) over to the last cell in the row with value in it - or would
that possibly overwrite something on the R1...R10 sheets?

#4 - If we still must skip blank cells in a row beyond column R, do we keep
the spacing on the destination sheets or do we pack them without empty cells
on the row?

In any case, then on each sheet (R1, R2...R10) our paste begins in column C,
with first entry in C5 and then continuing on down those sheets?


"Shu of AZ" wrote:

Using the data sheet below, how do I write a macro that would find the data
that extists between the first (#1) (C2), (there's a header), to the last
(#1) (C10), INCLUDING any blank rows there may be between them and copy then
paste ALL the data from column A to S, to another sheet named R1. NOTE: this
needs to exclude any blank colums past (R) and further(I excluded the data
just for ease).
After that it needs to come back to this data sheet and copy everything
between the (#2)'s and copy them to R2 and so on until it reaches no other
numbers usually around the number 10, then return to R1, cell (A2).
Note, I put hyphens between cell values in row 2 to indicate new adjacent
cell value.

The copy begins in column E and goes to column R
The top left cell on the sheet R1 is C5 where the paste begins

Thanks everyone.

x a b c d e f g h i j
1 Header
2 110-fm-1-Hol-81-11/01/2006-Easy Obsession-T-8.50-0.50
3 111 fst 1 Fpx 79 09/24/2006 Easy Obsession D 8.50 3.00
4 113 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
5 114 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
6 115 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
7 116 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
8 Blank row
9 117 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
10 118 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
11 119 fst 2 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
12 120 fst 2 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
13 121 fst 2 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00