Copying rows based on date value in an input box

[Luke94]

I'm looking for a macro that will copy rows of data based on a date value put in an input box and the name of the salesperson.

It is for a sales commission sheet and I am looking to copy rows of data from the previous week in one workbook and paste it to another for each salesperson/group to work out their order totals & commission for the week.

I have attached sample workbooks.

Any help would be appreciated

Commision Sample.xlsm
Sales Sample.xlsm

[ARowbot]

Can I clarify that data is being copied into the Commission Sample workbook?

Also, Where does the date come into it? Is it the "Pay week" in Cell F1 Or would you like to be able to enter a date specifically into an input box as the macro runs?

Basically I'm asking will it be a range of dates (a week) or just a specific day?

[Luke94]

Yes the data will be copied in to the Commission Sample workbook.

Yes it will be a week, so a range of dates. I would like to enter the dates for the previous week in an input box if possible.

Thanks

[ARowbot]

Here you go...You shall find the code in a module in the Commission sample workbook.


A few things to watch out for...
1. Because you've called the workbooks ...Sample, So have I in the code so you may need to change this as appropriate
2. Because of the limited space in the Commission workbook for records this may become a problem later..I have limited it to write into at maximum row 35.
3. It will currently not clear the previous data.

Have fun!

[Luke94]

Hi that's great thank you!

I've altered a couple of things and it works fine. Just one more thing...

How do I get the macro to run from the Commission Sample sheet? When I do run it from there it doesn't copy over any data?

Sorry I'm relatively new to this

[ARowbot]

It is already in the Commission Sample sheet and also the whole code refers to each workbook so shouldn't have a problem no matter where you run it!

What seems to be the problem? Is there an error or does it just not do anything?

[Luke94]

It just doesn't do anything

[ARowbot]

My bad... I missed the red bit..I've changed it slightly so that it reads nicer.

Please Login or Register  to view this content.

[Luke94]

Thanks, that's brilliant!