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

Shu,

Because you want copying & pasting to occur, a macro is better suited for
this solution.
--
http://HelpExcel.com




"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

He says in first line "how do I write a macro..."

And for once actually gives a pretty good desciption of requirements. If
someone hasn't posted solution by the time I get home 8 hrs or so from now,
I'll be looking at it.

"galimi" wrote:

Shu,

Because you want copying & pasting to occur, a macro is better suited for
this solution.
--
http://HelpExcel.com




"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

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

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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

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

No problem. I actually think what I offered up below takes care of
everything EXCEPT the change from C5 to U5 for the start of the paste
operation. I'm going to have to go in and dig around a little and test to
make sure.

It all deals with that section of code starting with
If lastColumn Range("R1").Column Then
specifically with setting the values for DPCO and down a little further with
the referenct to "C5". There is also an earlier reference to C5 that will
have to be changed.

I might even be able to make this a little more tailorable for you by
defining some Const values at the beginning of it all that you can change in
the future if your requirements are changed again. Like setting C5 (now U5)
as a constant, and doing some math to determine the value for the initial
DPCO value.

I'll probably upload the test workbook which will have the code in it and
you can copy right out of the code module in it into your own workbook.
Other than the changes to DPCO and the reference change from C5 to U5, I
think it'll be good to go.

As written we don't need to worry how far beyond column R you eventually
use, the code will pick up on the right-most used cell in any row and copy
over to that anyhow, although it's doing it one-cell at a time for cells
beyond column R.

"Shu of AZ" wrote:

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

I'm not sure how to thank you except to tell you thanks!!!!

"JLatham" wrote:

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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

No. I misspoke -- I'm still fuzzy on where to begin the copy.

#1 says that a value of 1 in column C means I must copy columns E:R (with
S-U optional),
But under #2 you say D:R. E or D?

I may be able to make that a definable Const also, and again do some math
later on to figure out offsets for DPCO as we move to the optional columns
for potential copying.


"Shu of AZ" wrote:

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

That pretty much says all that needs to be said.

Try this file:
http://www.jlathamsite.com/uploads/MoveInGroups.xls

One thing in code that you will definitely have to change,
Const sourceSheet = "Sheet1"
got to change that to the name of the sheet in your workbook with the data
on it when you move the code into it.

You can change any of the Const values you need to for your particular
circumstances. Right now it's set up to copy from E:R (and both are
definable) plus any extras beyond R that you end up using, and the paste goes
in starting at U5 on the R# sheets, and the offsets to deal with any copying
done beyond column R of the source sheet is now done with some math, so
that's handled automatically.

You can test in this workbook, just remember, when it's done, you're going
to be looking at cell A2 on the R# sheets, and there's nothing on the two
sheets created other than the copied data - which is way over at column U.

Enjoy.

"Shu of AZ" wrote:

I'm not sure how to thank you except to tell you thanks!!!!

"JLatham" wrote:

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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

I've posted to your help site. If not received, please let me know.

"JLatham" wrote:

That pretty much says all that needs to be said.

Try this file:
http://www.jlathamsite.com/uploads/MoveInGroups.xls

One thing in code that you will definitely have to change,
Const sourceSheet = "Sheet1"
got to change that to the name of the sheet in your workbook with the data
on it when you move the code into it.

You can change any of the Const values you need to for your particular
circumstances. Right now it's set up to copy from E:R (and both are
definable) plus any extras beyond R that you end up using, and the paste goes
in starting at U5 on the R# sheets, and the offsets to deal with any copying
done beyond column R of the source sheet is now done with some math, so
that's handled automatically.

You can test in this workbook, just remember, when it's done, you're going
to be looking at cell A2 on the R# sheets, and there's nothing on the two
sheets created other than the copied data - which is way over at column U.

Enjoy.

"Shu of AZ" wrote:

I'm not sure how to thank you except to tell you thanks!!!!

"JLatham" wrote:

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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

Thats the add I used from your button on the spreadsheet.

"JLatham" wrote:

I'll go check on that - best way to get to me for things relating to Excel
issues, especially those that began in these forums is via email to
HelpFrom @ jlathamsite.com (no spaces).


"Shu of AZ" wrote:

I've posted to your help site. If not received, please let me know.

"JLatham" wrote:

That pretty much says all that needs to be said.

Try this file:
http://www.jlathamsite.com/uploads/MoveInGroups.xls

One thing in code that you will definitely have to change,
Const sourceSheet = "Sheet1"
got to change that to the name of the sheet in your workbook with the data
on it when you move the code into it.

You can change any of the Const values you need to for your particular
circumstances. Right now it's set up to copy from E:R (and both are
definable) plus any extras beyond R that you end up using, and the paste goes
in starting at U5 on the R# sheets, and the offsets to deal with any copying
done beyond column R of the source sheet is now done with some math, so
that's handled automatically.

You can test in this workbook, just remember, when it's done, you're going
to be looking at cell A2 on the R# sheets, and there's nothing on the two
sheets created other than the copied data - which is way over at column U.

Enjoy.

"Shu of AZ" wrote:

I'm not sure how to thank you except to tell you thanks!!!!

"JLatham" wrote:

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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

Dinggg! You have email...

"Shu of AZ" wrote:

Thats the add I used from your button on the spreadsheet.

"JLatham" wrote:

I'll go check on that - best way to get to me for things relating to Excel
issues, especially those that began in these forums is via email to
HelpFrom @ jlathamsite.com (no spaces).


"Shu of AZ" wrote:

I've posted to your help site. If not received, please let me know.

"JLatham" wrote:

That pretty much says all that needs to be said.

Try this file:
http://www.jlathamsite.com/uploads/MoveInGroups.xls

One thing in code that you will definitely have to change,
Const sourceSheet = "Sheet1"
got to change that to the name of the sheet in your workbook with the data
on it when you move the code into it.

You can change any of the Const values you need to for your particular
circumstances. Right now it's set up to copy from E:R (and both are
definable) plus any extras beyond R that you end up using, and the paste goes
in starting at U5 on the R# sheets, and the offsets to deal with any copying
done beyond column R of the source sheet is now done with some math, so
that's handled automatically.

You can test in this workbook, just remember, when it's done, you're going
to be looking at cell A2 on the R# sheets, and there's nothing on the two
sheets created other than the copied data - which is way over at column U.

Enjoy.

"Shu of AZ" wrote:

I'm not sure how to thank you except to tell you thanks!!!!

"JLatham" wrote:

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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

dingg! right back at ya.

"JLatham" wrote:

Dinggg! You have email...

"Shu of AZ" wrote:

Thats the add I used from your button on the spreadsheet.

"JLatham" wrote:

I'll go check on that - best way to get to me for things relating to Excel
issues, especially those that began in these forums is via email to
HelpFrom @ jlathamsite.com (no spaces).


"Shu of AZ" wrote:

I've posted to your help site. If not received, please let me know.

"JLatham" wrote:

That pretty much says all that needs to be said.

Try this file:
http://www.jlathamsite.com/uploads/MoveInGroups.xls

One thing in code that you will definitely have to change,
Const sourceSheet = "Sheet1"
got to change that to the name of the sheet in your workbook with the data
on it when you move the code into it.

You can change any of the Const values you need to for your particular
circumstances. Right now it's set up to copy from E:R (and both are
definable) plus any extras beyond R that you end up using, and the paste goes
in starting at U5 on the R# sheets, and the offsets to deal with any copying
done beyond column R of the source sheet is now done with some math, so
that's handled automatically.

You can test in this workbook, just remember, when it's done, you're going
to be looking at cell A2 on the R# sheets, and there's nothing on the two
sheets created other than the copied data - which is way over at column U.

Enjoy.

"Shu of AZ" wrote:

I'm not sure how to thank you except to tell you thanks!!!!

"JLatham" wrote:

I hope this code does what you want. Tried to build it flexible enough to
meet your needs regardless of answers to my questions earlier.

There are two Const values at the beginning - you need to change those based
on reality at your end.

The code will create an R# sheet if required at any phase of the process, if
such a sheet already exists, it uses it, but always starts pasting at C5 even
if previous information is on that sheet.

Farther down into the code you'll find reference to variable DPCO, the lines
both look like
DPCO = DPCO+1
one is active, the other is inactive (has ' at start of it) Depending on
which of those you leave active, you'll either preserve blank cells past
column R or not. Only one of the entries should be active at any given time.

Sub MoveInGroups()
'change these two Const values as required
'next is sheet name with sourceData on it
Const sourceSheet = "Sheet1"
'next is first column to copy, change to A or E as needed
Const firstColumn = "E"

Dim lastRow As Long
Dim lastColumn As Long ' prep for O2K7
Dim destSheet As String
Dim DPRO As Long ' Destination Page Row Offset
Dim SPCO As Long ' Source Page Column Offset
Dim DPCO As Long ' Destination Page Column Offset
Dim currentGroup As Integer
Dim LC As Long
Dim RC As Long
Dim dummyTest As Variant

On Error Resume Next
lastRow = Worksheets(sourceSheet).Range("C" & _
Rows.CountLong).End(xlUp).Row
If Err < 0 Then
lastRow = Worksheets(sourceSheet).Range("C" _
& Rows.Count).End(xlUp).Row
Err.Clear
End If
On Error GoTo 0
For LC = 1 To lastRow - 1
Worksheets(sourceSheet).Range("C1").Select
If Not IsEmpty(ActiveCell.Offset(LC, 0)) Then
destSheet = "R" & ActiveCell.Offset(LC, 0)
If ActiveCell.Offset(LC, 0) < currentGroup Then
currentGroup = ActiveCell.Offset(LC, 0)
DPRO = 0 ' reset
End If
End If
Range(firstColumn & ActiveCell.Offset(LC, 0).Row & ":R" _
& ActiveCell.Offset(LC, 0).Row).Copy
'either go to the 'R#' sheet or create one if it doesn't exist
On Error Resume Next
dummyTest = Worksheets(destSheet).Range("A1").Value
Application.ScreenUpdating = False
If Err < 0 Then
'need to create the sheet
Sheets.Add
ActiveSheet.Name = destSheet
ActiveSheet.Range("A2").Select
Worksheets(sourceSheet).Select
Err.Clear
Else
Worksheets(destSheet).Activate
Range("A2").Select
End If
On Error GoTo 0
Worksheets(sourceSheet).Select
Range("C1").Select
Application.ScreenUpdating = True
Worksheets(destSheet).Range("C5").Offset(DPRO, 0).PasteSpecial _
xlPasteValues
'now have to find if there are more cells to copy from
'source row to dest row
'test if we need to do any of this at all
'if nothing beyond Column R (column #18), nothing to do
lastColumn = _
Worksheets(sourceSheet).Range("IV" & ActiveCell.Offset(LC, 0).Row). _
End(xlToLeft).Column
If lastColumn Range("R1").Column Then
'yes, something out there somewhere
SPCO = 1
If firstColumn = "E" Then
DPCO = 14
Else
DPCO = 16
End If
For RC = 18 To lastColumn
If Not IsEmpty(Worksheets(sourceSheet).Range("R" & _
ActiveCell.Offset(LC, RC).Row).Offset(0, SPCO)) Then
Worksheets(destSheet).Range("C5").Offset(DPRO, DPCO) = _
Worksheets(sourceSheet).Range("R" & ActiveCell. _
Offset(LC, RC).Row).Offset(0, SPCO)
' if active here, does not move empty cells
'DPCO = DPCO + 1
End If
SPCO = SPCO + 1
' if active here, not above, preserves empty cells
DPCO = DPCO + 1
Next
End If
'
DPRO = DPRO + 1
Next ' LC loop
Application.CutCopyMode = False
Range("A1").Select

'need to go to sheet R1 specifically? Activate this line
'just make sure it will always exist
'Worksheets("R1").Select
End Sub

"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