copy partial worksheet to a different worksheet based on date??

[Kevin J Prince]

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

 

[Don Guillett]

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

 

[Kevin J Prince]

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

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

 

[Don Guillett]

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

 

[Kevin J Prince]

Thanks Don,

I've done that

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