Shorter macro to move all lines based on cell value

[daveb86]

Hello Guys

Im needing a little help with something ...

Basically i want to copy and paste all data in a row if the value in a cell is equal to something

Im currently using the below formula

Please Login or Register  to view this content.

But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be a pain

So im looking for a way i can add multiple values each going to seperate sheets

Thanks

Dave

[Tsjallie]

You could put these 37 values somewhere in a table and have the macro loop thru this table until all the 37 cycles have been done.

[daveb86]

You could put these 37 values somewhere in a table and have the macro loop thru this table until all the 37 cycles have been done.

Would you be able to give me an example of what you mean ?

[Tsjallie]

Sure I can, but will need your workbook. Can you upload that?

[Tsjallie]

Added some code(in red) to your macro.
Should do the job provided that you create the named range "CriteriaTable" containing the criteria for the filter somewhere in your workbook.

Please Login or Register  to view this content.

[daveb86]

Added some code(in red) to your macro.
Should do the job provided that you create the named range "CriteriaTable" containing the criteria for the filter somewhere in your workbook.

Please Login or Register  to view this content.

Thanks for that ..

What format would i need to layout the table ?

As id like each criteria to be moved to its own separate sheet

And as for

lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row '<= BTW, why not use src.usedrange.rows.count?

What would be the difference between the two ?

[Tsjallie]

Additions and edits are in red.

Please Login or Register  to view this content.

The CriteriaTable consists of 2 columns, but only the column containing the criteria is defined as the CriteriaTable-range in this procedure.
That is because Criterion is only one cell.

The Criteria table in the sheet may look like this:
CriteriaTable.JPG

And, I was just wondering if you had a specific reason for not using usedrange

[daveb86]

Additions and edits are in red.

Please Login or Register  to view this content.

The CriteriaTable consists of 2 columns, but only the column containing the criteria is defined as the CriteriaTable-range in this procedure.
That is because Criterion is only one cell.

The Criteria table in the sheet may look like this:
Attachment 324419

And, I was just wondering if you had a specific reason for not using usedrange

The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion)

What am I putting in here ?

And I got this code from someone here a while back so I have no idea

Also whilst on the subject how would I get this to work on a date ?

For example: The data sheet has a date in the format of dd/mm/yyyy (10/06/2014) in Cell A2

How would I get this macro to pull all lines from the drop sheet that have the date that's entered in a cell on another sheet

[Tsjallie]

Could you please upload your workbook.
Makes it much easier for me to makes thing clear.

[daveb86]

Ok i have uploaded the workbook im currently working on and added some example data

Expected Delivery Summary Test.xls

Basically what i need is if you look on the summary page you will see the Date for each day along the top

I want a macro to move all lines from the Dump Sheet to the relevant day that date falls on

EG: Monday is down as being 06/09/2014 .. I want all lines from the dump sheet with that date to be moved into the Monday tab

Also on the summary page i need to know the number of SKU's and Suppliers these will be duplicated so i only the count of unique values and i need a formula to calculate that based on each individual department.

Thanks

Dave

[Tsjallie]

Thanks for the workbook. Think I can make something usefull out of that.

One question though:
Can I clean up the Dump Sheet after all the rows have been copied?
If not, I need to add something to make sure rows are copied only once and duplicates in the Day sheets are avoided.

Will work on the copying first.

[daveb86]

Thanks for the workbook. Think I can make something usefull out of that.

One question though:
Can I clean up the Dump Sheet after all the rows have been copied?
If not, I need to add something to make sure rows are copied only once and duplicates in the Day sheets are avoided.

Will work on the copying first.

Yeah sure ideally all the lines from the dump sheet should be moved into the relevant tabs for that date / day combination

Anything else that is left in there can be cleared out so its left empty

[Tsjallie]

Here's the edited sheet.
Created two enterily new macros in the module modSubsAndFunctions and attached it to the button on the Summary Sheet:
DistributeRows() which is the main macro and is started by the button.
DistributeRows() calls a submacro CopyDumpRow(). Saves duplicate code.
I think it works as intended.

Some remarks:
- there's something with the format of some dates in the Dump Sheet my Excel version (Dutch) can't handle correctly, but vba does.
- added a column (Copied to ...) in the Dump Sheet showing where the row has been copied to. Just for you to facilitate checking.

Will take a look at this now:

Also on the summary page i need to know the number of SKU's and Suppliers these will be duplicated so i only the count of unique values and i need a formula to calculate that based on each individual department.

[daveb86]

Thanks for that Tsjallie

There is a small adjustment to be made on the macro though .. When calculating the information for the summary page you will notice that some of the data on the dump sheet is duplicated

For Example (Re-Attached a copy of the Dump Sheet)

Book1.xls

The highlighted data is all on the same Master Receipt and Address Name so in total there are only 26 Pallets, But this is displayed for each order line, so this is a unique count based on the Receipt and Address.

If you could make an alteration for that and find a way to make sure the values for the part you quoted above are also unique and not duplicated that would be excellent

Also on the summary page i need to know the number of SKU's and Suppliers these will be duplicated so i only the count of unique values and i need a formula to calculate that based on each individual department.

Thanks

Dave

[Tsjallie]

I must say you completely lost me
Below are the things that puzzle me

The highlighted data is all on the same Master Receipt and Address Name

Master Receipt is in column F or column H?

so in total there are only 26 Pallets

How do you get to the number of 26?

Most important is that you explain how I can identify the duplicates in the Dump sheet.

[daveb86]

I must say you completely lost me
Below are the things that puzzle me
Master Receipt is in column F or column H?
How do you get to the number of 26?

Most important is that you explain how I can identify the duplicates in the Dump sheet.

Yeah so the master receipt is in Column F and the Address name is in Column H

So basically if the lines have the same Master Receipt and the same Address name then the pallet count will be the same in Column Q

But because we have multiple items on each receipt they will all be spread across the same number of pallets

As an example the lines i have highlighted all say 48 pallets that means that there maybe 10 lines all distributed across these 48 pallets

So there will be a unique count of pallets for each Master reciept and Department .. So in this case

Master Receipt is DA041016
Department is Bath and Bedroom
Pallets = 48
SKU's = 10

If that makes sense .. Obviously the counts will be different for each Department and each Master receipt.

[Tsjallie]

Ok, I think that's clear now.
I assume that the copying of the rows can stay as it is now and the issue with the duplicates can be solved with the counts in the Summary sheet.

Still breaking my head over the unique counts.
Not sure if I will be doing this with a formula or with a user defined function. A formula will most probably be a rather ugly one.

[Tsjallie]

Hi Dave,
Here's a new version with formulas for the unique counts of SKU's and suppliers for each department (in the Monday section of the Summary sheet).
They are ugly, but I must say not as ugly as I anticipated

I believe there remains one issue still, but not sure what that was.
And probably new issues will pop up

BTW, what actually is a SKU?

[daveb86]

Hi Dave,
Here's a new version with formulas for the unique counts of SKU's and suppliers for each department (in the Monday section of the Summary sheet).
They are ugly, but I must say not as ugly as I anticipated

I believe there remains one issue still, but not sure what that was.
And probably new issues will pop up

BTW, what actually is a SKU?

Are you able to remove the clean up code so the Monday to Friday tabs aren't cleared ?

When these are cleared it doesn't calculate the No of SKU's and Suppliers

And a SKU is what is known as a Stock Keeping Unit .. Its similar to an item number or a barcode number on an item

Thanks

Dave

[Tsjallie]

Are you able to remove the clean up code so the Monday to Friday tabs aren't cleared ?

There's no clean up code.
What happens is that Excel does not recalculate the usedrange of a sheet when rows are deleted and for testing that's what I've been doing in the Monday sheet. Consequently a new copy run inserts rows in the Monday sheet starting on the wrong row.
This bug slipped my mind and is very simple to work around. Did that in attached workbook.
Don't worry about the #Value errors. They're there because the day sheets are empty and will go away as soon as data is copied in.

Other thing that I forgot to mention in my previous post, is that I decreased the number of rows the formulas are calculating. Calculating the whole column takes ages.
Solved this by using named ranges which refer to only the data rows. The ranges are adjusted each time a row is inserted.

Please notice that the Dump sheet is not cleared yet after copying.
As soon as you are confident that thing work OK I can make alter the procedure to clean up that sheet after it's done.

[daveb86]

This wont work for me seems to be an issue with the line below

Please Login or Register  to view this content.

All it does it copies across 1 line for Monday then stops at the line above in the macro below

Please Login or Register  to view this content.

[Tsjallie]

Is there an error message?

I can't reproduce this, but I'm running this 2003 workbook in Excel 2010 in compatibilty mode.
May be there's something in the workbook Excel 2003 can't handle.

Do have the opportunity to run the workbook in Excel 2010?

[Tsjallie]

If you replace .usedrange.rows.countlarge by .usedrange.rows.count in the lines for deptcolumn, suppcolumn and itemcolumn the problem should be solved.
Countlarge is not available in Excel 2003.

[daveb86]

If you replace .usedrange.rows.countlarge by .usedrange.rows.count in the lines for deptcolumn, suppcolumn and itemcolumn the problem should be solved.
Countlarge is not available in Excel 2003.

That seems to work fine now ..

Are you able to add in the functionality so that the information for each day is copied into the respective days tab ?

At the moment its either not copied across or it is cleared once it has been calculated

This is so I can calculate the SKU's and Suppliers

[Tsjallie]

Good to hear that it working now.
BTW, wouldn't it be time to upgrade to Excel 2010 or 2013?
And are you still running it on Windows XP?

the information for each day is copied into the respective days tab

Do you mean the days in the Summary sheet?. I only did the calculation there for Monday.
If the calculations for Monday are OK now I can do the other days too.
And I will make the procedure clear the Dump sheet after copying has been done.

[daveb86]

Good to hear that it working now.
BTW, wouldn't it be time to upgrade to Excel 2010 or 2013?
And are you still running it on Windows XP?

It would ... But its a work PC so that could happen between now and 3014 and nope windows server 03 edit

Do you mean the days in the Summary sheet?. I only did the calculation there for Monday.
If the calculations for Monday are OK now I can do the other days too.
And I will make the procedure clear the Dump sheet after copying has been done.

Yeah so where it says "Copied to Monday" actually place those lines in Monday's tab and make them stay in there so if needed they can be looked at ..

I'm not sure the information is correct as if you look at Monday the pallet count is incorrect as this shouldn't be duplicated for each DA / department as I have just ran it with today's updated information and 1,273 pallets in one day seems far too many

And thanks long as the information cleared from the dump sheet is available in the daily tabs

[Tsjallie]

Here's the workbook with all the day tabs in the Summary sheet calculated.
Dump sheet is cleaned up after all the rows are copied.

BTW, there are still rows which are not copied, because the date is a Saturday.
Shouldn't they be accounted for too?

Thanks for the credits

[daveb86]

Can you change the macro to only copy in the text "Copied to Monday" based on the date ?

I don't need it to actually do anything else apart from that

E.G. no Calculating or inputting into the correct sheet

A basic "Copied to Monday" or which ever day it should go in ?

Thanks

Dave

[Tsjallie]

Can you change the macro to only copy in the text "Copied to Monday" based on the date ?

As you wish
Commented out the calls to the copy-routine.

[daveb86]

As you wish
Commented out the calls to the copy-routine.

Thanks thats perfect :D

2nd issue is the date is in the following format .. MM/DD/YYYY

Are you able to modify the code slightly so the days are correct ?

As this one "07/02/2014 06:00" is being considered to be 7th Feb when its in fact the 2nd July

07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 06:00
07/02/2014 10:00
07/02/2014 14:00
07/02/2014 14:00
07/02/2014 14:00
07/02/2014 14:00
07/02/2014 14:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00
07/03/2014 06:00

[Tsjallie]

If - in the sub DistributeData() - you replace the line

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.

that should do the trick.
Using "-" or "/" doesn't make a difference.