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

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

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

Hi Greg
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?

Not automatically, but using 'Advanced Filters' enables you to copy the
result on a separate worksheet. This may want your are looking for


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?

Not sure what you're trying to do. Do you want to automatically change
the result of a cell, filled by a listbox (data validation) after
choosing an entry. Maybe you can give a small example for this

Frank

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

Thanks for your help!
learned about concatenate today which seems to work for one of my projects!

The copying and pasting of the filtered info is harder... the result
references the formula for the info whose values I want to copy and I can't
use paste special to paste the data into a new workbook without pasting
somewhere else and then using array to create a new workbook and then copy
the two sheets I need into the new workbook. Any ideas? Is there a way to
copy the info to a temporary page and into the new workbook and control the
name of the sheet?

Your help is always appreciated!

Greg

"Frank Kabel" wrote in message
...
Hi Greg
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?

Not automatically, but using 'Advanced Filters' enables you to copy the
result on a separate worksheet. This may want your are looking for


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?

Not sure what you're trying to do. Do you want to automatically change
the result of a cell, filled by a listbox (data validation) after
choosing an entry. Maybe you can give a small example for this

Frank

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

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

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