This applies the filter, set the criteria in Column A to "A", copies the
data, removes the autofilter.

Sub CopyData()
Dim rng As Range
Dim rng1 As Range
Set rng = Worksheets("Data")
.Range("A1").CurrentRegion
rng.AutoFilter Field:=1, Criteria1:="A"
Set rng1 = rng.Offset(1, 0). _
Resize(rng.Rows.Count - 1)
rng1.Copy
Sheets("Sheet3").Range("A1") _
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
rng.AutoFilter
End Sub

change Data to the name of your source sheet and Sheet3 to the name of your
destination Sheet.

--
Regards,
Tom Ogilvy

Greg Liber wrote in message
...
Hi!

That works great, but is there a way to write that in a macro? Only copy
the rows with an entry in column "A"? I'm trying to make this as easy for
the users as possible... because when there's a way to mess up the form...
they find it...

Thanks again for all your help!

Greg
"Tom Ogilvy" wrote in message
...
I made a table that contained formulas. I filtered the table, selected
the
data (entire row), did edit=copy

when to another worksheet, selected a single cell in column a, did
Edit=PasteSpecial and selected values.

Only the visible rows were copied and they were pasted as values.

that sounds like what you want to do.

--
Regards,
Tom Ogilvy

Greg Liber wrote in message
...
thanks for your help!

Just learned how to use concatenate today! Worked great!

The filtered stuff is a little more difficult... I want to copy the
filtered
information from a sheet that the info for the filtered results is a
formula
and I need to copy it into a new sheet in a new workbook and I only
need
the
values in this workbook. (This is what we submit to our payroll
department
and they only need the values.) I can copy the info onto another
worksheet
using paste special values and then use array and copy the sheet (but
this
gives me #ref) or copy the entire section that the info might be
stored
(this varies depending on the payroll) but I'm trying to make the file
I
transmit as small as I can.

any ideas?

Thanks!
Greg
"Tom Ogilvy" wrote in message
...
For you first question, hard to tell what you mean by automatic, but
assume
you mean only the filtered data:
If you copy a filtered range, only the visible cells are copied.

Are you familiar with concatenate

=A1 & b1

=A1 & Left(b1,3)

=A1 & Text(B1,"000")

if B1 contains numbers

perhaps.

--
Regards,
Tom Ogilvy

Greg Liber wrote in message
...
THANKS!
That worked great!

Not to take advantage of your time... but is there a way to
automatically
copy only the cells in the sorted range that have the employee
number
in
column "A" that has been filtered?

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked
puts
a
G/L
code into a cell that is used to identify where to charge the
monies...
the
only difference in the G/L codes is that there is a 3 digit
restaurant
account number at the end of the G/L code... however this means a
fair
amount of work to set up a new account... is there a way to use
the
list
to
generate the G/L number by using the first part of the G/L code
that
is
common for all the info and just add the 3 digit account number on
the
back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

"Frank Kabel" wrote in message
...
Hi Greg
[snip]
Now the problem is that I can get the formulas to work on the
first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I
then
sort
to remove all the blank rows, the employee number then becomes
2
from
0002 (if under 999) which isn't too bad because I can format
the
columns to 0000 but it sorts with a ton of blank rows in
between.
The sheet is then copied onto a new form that I send to
payroll
so
I
cannot leave the blank rows. (The blank rows are payroll
items
that
aren't relevant to that employee's current payroll
information)
The
sorting and copying is all done with a macro if that helps.

One question: How do you reference the employee data (with a
simple
cell reference or something else)
Next question: If you only want to remove blank rows, probably
filtering (not sorting) would be the way to do this (goto '
Data -
Filter', choose 'Autofilter' and in the appearing drop down
choose
'Non
blanks')

Frank