Hi,

The subject says it all??

How do I copy information from one worksheet to another based on a date?

I have a worksheet which I am keeping a list of equipment, date bought,
what it is, how much it cost, chargeable or not and where it was bought
from.

Some of this equipment is chargeable to a customer some not.

When I write an invoice I want to insert items from the equipment list
which have chargeable as YES or Y into the new invoice, but only for the
items which are for the week of the invoice. (That's Monday to Saturday
of that week).

Once again I'm assuming a Macro of VBA but have no idea where to start

Worksheets are, 'Equipment', 'Template' and 'Invoice XX' where XX is the
number and there will be quite a few of them. But only the current one
is to be actioned on.

Oh yes equipment can be added right up to the actual printing of the
Invoice XX time. So I assume I would have to run the macro at that time
or have some mechanism for checking if it was already in the invoice???

I had never realised how powerful Excel was until now!

Regards

Kevin

--
"I live in my own little world.
But it's OK. They know me here."
= = = =
Kevin J Prince
http://www.1and1.co.uk/?k_id=5257507

One way is to use (record a macro while doing)
data>filter>autofilter>set your criteria>copy

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Kevin J Prince" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi,
>
> The subject says it all??
>
> How do I copy information from one worksheet to another based on a date?
>
> I have a worksheet which I am keeping a list of equipment, date bought,
> what it is, how much it cost, chargeable or not and where it was bought
> from.
>
> Some of this equipment is chargeable to a customer some not.
>
> When I write an invoice I want to insert items from the equipment list
> which have chargeable as YES or Y into the new invoice, but only for the
> items which are for the week of the invoice. (That's Monday to Saturday of
> that week).
>
> Once again I'm assuming a Macro of VBA but have no idea where to start
>
> Worksheets are, 'Equipment', 'Template' and 'Invoice XX' where XX is the
> number and there will be quite a few of them. But only the current one is
> to be actioned on.
>
> Oh yes equipment can be added right up to the actual printing of the
> Invoice XX time. So I assume I would have to run the macro at that time or
> have some mechanism for checking if it was already in the invoice???
>
> I had never realised how powerful Excel was until now!
>
> Regards
>
> Kevin
>
> --
> "I live in my own little world.
> But it's OK. They know me here."
> = = = =
> Kevin J Prince
> http://www.1and1.co.uk/?k_id=5257507

Hi Don,

Thanks for the reply, however I was hoping to be able to set it as a
macro. I'm setting this up for my son, he knows where the on off switch
is and how to run excel, but the knowledge stops there.

I think what I'm after is a macro which scans the equipment worksheet,
when it finds something that matches both date and chargeable copies a
set of cells into another worksheet. Well actually inserts a line then
insert the required cells into that set of cells.

It's obviously going to involve some IF's etc, but I don't know how to
reference the different cells from within the same IF. If you know what
I mean. I'm OK with basic VB stuff, but how do you reference the
different cells and sheets. Is where I'm getting stuck.

For example, how do I know if I've reached the last entry in the
equipment sheet? While I'm parsing that is.....

Do while (somethow not eof??)
IF cell(Bx)= (existing date checking for) AND cell(Fx) = "Y"
then
(Do the transfer somehow)
Invoice.worksheet.cell(Ry)= equipment.worksheet.cell(Bx)
endif
Loop

In the above, how do I reference cell(Bx) the column part?

etc??

Help much appreciated please..... Any tutorial refs would also be useful
please.

Kevin



In message <edyDb#(E-Mail Removed)>, Don Guillett
<(E-Mail Removed)> writes
>One way is to use (record a macro while doing)
>data>filter>autofilter>set your criteria>copy
>

--
"I live in my own little world.
But it's OK. They know me here."
= = = =
Kevin J Prince
http://www.1and1.co.uk/?k_id=5257507

Feel free to send a workbook along with copies of these posts and
before/afer samples to the address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Kevin J Prince" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Don,
>
> Thanks for the reply, however I was hoping to be able to set it as a
> macro. I'm setting this up for my son, he knows where the on off switch
> is and how to run excel, but the knowledge stops there.
>
> I think what I'm after is a macro which scans the equipment worksheet,
> when it finds something that matches both date and chargeable copies a
> set of cells into another worksheet. Well actually inserts a line then
> insert the required cells into that set of cells.
>
> It's obviously going to involve some IF's etc, but I don't know how to
> reference the different cells from within the same IF. If you know what
> I mean. I'm OK with basic VB stuff, but how do you reference the
> different cells and sheets. Is where I'm getting stuck.
>
> For example, how do I know if I've reached the last entry in the
> equipment sheet? While I'm parsing that is.....
>
> Do while (somethow not eof??)
> IF cell(Bx)= (existing date checking for) AND cell(Fx) = "Y"
> then
> (Do the transfer somehow)
> Invoice.worksheet.cell(Ry)= equipment.worksheet.cell(Bx)
> endif
> Loop
>
> In the above, how do I reference cell(Bx) the column part?
>
> etc??
>
> Help much appreciated please..... Any tutorial refs would also be useful
> please.
>
> Kevin
>
>
>
> In message <edyDb#(E-Mail Removed)>, Don Guillett
> <(E-Mail Removed)> writes
>>One way is to use (record a macro while doing)
>>data>filter>autofilter>set your criteria>copy
>>
>
> --
> "I live in my own little world.
> But it's OK. They know me here."
> = = = =
> Kevin J Prince
> http://www.1and1.co.uk/?k_id=5257507

Thanks Don,

I've done that


In message <#(E-Mail Removed)>, Don Guillett
<(E-Mail Removed)> writes
>Feel free to send a workbook along with copies of these posts and
>before/afer samples to the address below.
>

--
"I live in my own little world.
But it's OK. They know me here."
= = = =
Kevin J Prince
http://www.1and1.co.uk/?k_id=5257507