I have an input range of potentially 200 rows of daily data. I want to copy
only those records with data and add them to a perpetual range of data for
the year. This year data grows. How do I use a macro using an advanced
filter to extract the records and add them to the other range of data?

The parameters given leavea lot of holes, but if I were to do something like
that I would first make sure that I had a criteria to select the items that
would prevent duplicates. Probably a columhn with an entry date. Then I
would use the date as one of the criteria to fo filter out the records I
wanted. Then I would copy those visible records from the filter to the new
list.


"Joe" wrote in message
...
I have an input range of potentially 200 rows of daily data. I want to
copy
only those records with data and add them to a perpetual range of data for
the year. This year data grows. How do I use a macro using an advanced
filter to extract the records and add them to the other range of data?

Joe
You say "I want to copy only those records with data...". What do the other
records contain? If you want to copy only those records that meet specific
criteria, what is the specific criteria? HTH Otto
"Joe" wrote in message
...
I have an input range of potentially 200 rows of daily data. I want to
copy
only those records with data and add them to a perpetual range of data for
the year. This year data grows. How do I use a macro using an advanced
filter to extract the records and add them to the other range of data?

OK - here is more detail - I know how to extract records using advance
filters. I know how to put them in a blank set of records. The question is
- how do I add them to another set of records and I want to use a macro to do
it. The number of rows keeps expanding so I don't have the same row number
to use. I know I need the same column headers but I don't want to replace
the old records I want to add new records to the old records.

"Joe" wrote:

I have an input range of potentially 200 rows of daily data. I want to copy
only those records with data and add them to a perpetual range of data for
the year. This year data grows. How do I use a macro using an advanced
filter to extract the records and add them to the other range of data?

Joe
I still don't have a clear understanding of what you want, but maybe
this macro will get you started. I assumed that the sheet you are copying
FROM is the active sheet, and the sheet into which to paste is named "Two".
This macro will copy visible cells only. It will copy the range of Column A
from row 2 down, and 10 columns wide, and it will paste this into the first
empty cell in Column A in sheet "Two". HTH Otto
Sub CopyVisible()
Dim rColA As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 10).SpecialCells(xlCellTypeVisible).Copy Dest
End With
End Sub
"Joe" wrote in message
...
OK - here is more detail - I know how to extract records using advance
filters. I know how to put them in a blank set of records. The question
is
- how do I add them to another set of records and I want to use a macro to
do
it. The number of rows keeps expanding so I don't have the same row
number
to use. I know I need the same column headers but I don't want to replace
the old records I want to add new records to the old records.

"Joe" wrote:

I have an input range of potentially 200 rows of daily data. I want to
copy
only those records with data and add them to a perpetual range of data
for
the year. This year data grows. How do I use a macro using an advanced
filter to extract the records and add them to the other range of data?

OK - I am starting to understand there is no direct way to combine ranges.
So if, I have 200 rows with only 10 rows with data I want to copy and not all
cells are visible is there a way to take rows with a field that contains a
value 0? Or should I extract the input data of 200 rows to a temp range
containing the 10 rows that match my criteria and then use your copy routine?

"Otto Moehrbach" wrote:

Joe
I still don't have a clear understanding of what you want, but maybe
this macro will get you started. I assumed that the sheet you are copying
FROM is the active sheet, and the sheet into which to paste is named "Two".
This macro will copy visible cells only. It will copy the range of Column A
from row 2 down, and 10 columns wide, and it will paste this into the first
empty cell in Column A in sheet "Two". HTH Otto
Sub CopyVisible()
Dim rColA As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 10).SpecialCells(xlCellTypeVisible).Copy Dest
End With
End Sub
"Joe" wrote in message
...
OK - here is more detail - I know how to extract records using advance
filters. I know how to put them in a blank set of records. The question
is
- how do I add them to another set of records and I want to use a macro to
do
it. The number of rows keeps expanding so I don't have the same row
number
to use. I know I need the same column headers but I don't want to replace
the old records I want to add new records to the old records.

"Joe" wrote:

I have an input range of potentially 200 rows of daily data. I want to
copy
only those records with data and add them to a perpetual range of data
for
the year. This year data grows. How do I use a macro using an advanced
filter to extract the records and add them to the other range of data?

Joe
The following macro loops through all the values in Column A of the
active sheet. If a value is 5 it copies 10 columns in that row, starting
with Column A, and pastes it to the first empty cell in Column A of sheet
"Two". Post back if you need more. Otto

Sub CopySome()
Dim rColA As Range, Dest As Range, i As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
If i 5 Then
i.Resize(, 10).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub
"Joe" wrote in message
...
OK - I am starting to understand there is no direct way to combine ranges.
So if, I have 200 rows with only 10 rows with data I want to copy and not
all
cells are visible is there a way to take rows with a field that contains a
value 0? Or should I extract the input data of 200 rows to a temp range
containing the 10 rows that match my criteria and then use your copy
routine?

"Otto Moehrbach" wrote:

Joe
I still don't have a clear understanding of what you want, but maybe
this macro will get you started. I assumed that the sheet you are
copying
FROM is the active sheet, and the sheet into which to paste is named
"Two".
This macro will copy visible cells only. It will copy the range of
Column A
from row 2 down, and 10 columns wide, and it will paste this into the
first
empty cell in Column A in sheet "Two". HTH Otto
Sub CopyVisible()
Dim rColA As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 10).SpecialCells(xlCellTypeVisible).Copy Dest
End With
End Sub
"Joe" wrote in message
...
OK - here is more detail - I know how to extract records using advance
filters. I know how to put them in a blank set of records. The
question
is
- how do I add them to another set of records and I want to use a macro
to
do
it. The number of rows keeps expanding so I don't have the same row
number
to use. I know I need the same column headers but I don't want to
replace
the old records I want to add new records to the old records.

"Joe" wrote:

I have an input range of potentially 200 rows of daily data. I want
to
copy
only those records with data and add them to a perpetual range of data
for
the year. This year data grows. How do I use a macro using an
advanced
filter to extract the records and add them to the other range of data?

OK - this gets more and more interesting. I thought i knew a lot about Excel
but VBA is a whole new world.
So I am thinking of the following changes.
1. I want to check col 16 for 0 so == If i 0 then
but where in the Set rColA do I change to 16? is it "A2" to "A16"?
2. I have 23 columns so== i.resize(,23).copy dest
3. Also the input range starts in col P but also is named "INPUT_DB" can /
how would I change the rColA for a range name.

Thanks in advance for your answer and this one should complete it.
"Otto Moehrbach" wrote:

Joe
The following macro loops through all the values in Column A of the
active sheet. If a value is 5 it copies 10 columns in that row, starting
with Column A, and pastes it to the first empty cell in Column A of sheet
"Two". Post back if you need more. Otto

Sub CopySome()
Dim rColA As Range, Dest As Range, i As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
If i 5 Then
i.Resize(, 10).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub
"Joe" wrote in message
...
OK - I am starting to understand there is no direct way to combine ranges.
So if, I have 200 rows with only 10 rows with data I want to copy and not
all
cells are visible is there a way to take rows with a field that contains a
value 0? Or should I extract the input data of 200 rows to a temp range
containing the 10 rows that match my criteria and then use your copy
routine?

"Otto Moehrbach" wrote:

Joe
I still don't have a clear understanding of what you want, but maybe
this macro will get you started. I assumed that the sheet you are
copying
FROM is the active sheet, and the sheet into which to paste is named
"Two".
This macro will copy visible cells only. It will copy the range of
Column A
from row 2 down, and 10 columns wide, and it will paste this into the
first
empty cell in Column A in sheet "Two". HTH Otto
Sub CopyVisible()
Dim rColA As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 10).SpecialCells(xlCellTypeVisible).Copy Dest
End With
End Sub
"Joe" wrote in message
...
OK - here is more detail - I know how to extract records using advance
filters. I know how to put them in a blank set of records. The
question
is
- how do I add them to another set of records and I want to use a macro
to
do
it. The number of rows keeps expanding so I don't have the same row
number
to use. I know I need the same column headers but I don't want to
replace
the old records I want to add new records to the old records.

"Joe" wrote:

I have an input range of potentially 200 rows of daily data. I want
to
copy
only those records with data and add them to a perpetual range of data
for
the year. This year data grows. How do I use a macro using an
advanced
filter to extract the records and add them to the other range of data?

Joe
The following macro works with Column P (the 16th column). Don't forget
that the sheet with the data you want to copy must be the active sheet. The
line:
i.Offset(, -15).Resize(, 23).Copy Dest
Means start with i, which is in Column P, offset left 15 columns, that's
Column A, resize to 23 columns. Copy those 23 columns in the row of i and
paste it to the Dest which is in sheet "Two" and starts with A2.
If the range of all the occupied cells in Column P, from P2 down, has a
range name of INPUT_DB, then you can substitute Range("INPUT_DB") for rColP
and do away with the line that starts with Set rColP =............ Otto
Sub CopySome()
Dim rColP As Range, Dest As Range, i As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
If i 0 Then
i.Offset(, -15).Resize(, 23).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub
"Joe" wrote in message
...
OK - this gets more and more interesting. I thought i knew a lot about
Excel
but VBA is a whole new world.
So I am thinking of the following changes.
1. I want to check col 16 for 0 so == If i 0 then
but where in the Set rColA do I change to 16? is it "A2" to "A16"?
2. I have 23 columns so== i.resize(,23).copy dest
3. Also the input range starts in col P but also is named "INPUT_DB" can
/
how would I change the rColA for a range name.

Thanks in advance for your answer and this one should complete it.
"Otto Moehrbach" wrote:

Joe
The following macro loops through all the values in Column A of the
active sheet. If a value is 5 it copies 10 columns in that row,
starting
with Column A, and pastes it to the first empty cell in Column A of sheet
"Two". Post back if you need more. Otto

Sub CopySome()
Dim rColA As Range, Dest As Range, i As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
If i 5 Then
i.Resize(, 10).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub
"Joe" wrote in message
...
OK - I am starting to understand there is no direct way to combine
ranges.
So if, I have 200 rows with only 10 rows with data I want to copy and
not
all
cells are visible is there a way to take rows with a field that
contains a
value 0? Or should I extract the input data of 200 rows to a temp
range
containing the 10 rows that match my criteria and then use your copy
routine?

"Otto Moehrbach" wrote:

Joe
I still don't have a clear understanding of what you want, but
maybe
this macro will get you started. I assumed that the sheet you are
copying
FROM is the active sheet, and the sheet into which to paste is named
"Two".
This macro will copy visible cells only. It will copy the range of
Column A
from row 2 down, and 10 columns wide, and it will paste this into the
first
empty cell in Column A in sheet "Two". HTH Otto
Sub CopyVisible()
Dim rColA As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(, 1)
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 10).SpecialCells(xlCellTypeVisible).Copy Dest
End With
End Sub
"Joe" wrote in message
...
OK - here is more detail - I know how to extract records using
advance
filters. I know how to put them in a blank set of records. The
question
is
- how do I add them to another set of records and I want to use a
macro
to
do
it. The number of rows keeps expanding so I don't have the same row
number
to use. I know I need the same column headers but I don't want to
replace
the old records I want to add new records to the old records.

"Joe" wrote:

I have an input range of potentially 200 rows of daily data. I
want
to
copy
only those records with data and add them to a perpetual range of
data
for
the year. This year data grows. How do I use a macro using an
advanced
filter to extract the records and add them to the other range of
data?