Copy cell value based on a date to a specific colum

Hi

I have played around with a few different ways to do this and I can't get any to work well.

What I have is a 2 sheets, on sheet 2 there is a column with dates in (these could be mon-fri). On sheet one I have a list of Łamounts. With the header Amount and then for the rest of the year I have the Week Commencing (MON) dates as headers in ROW A

What I need is a macro that will work through the entire row of dates in sheet 2. Whatever the date of the Monday of that week is I need it to copy and paste the Łamount from sheet 1 and put the amount in the correct cell of the correct row.

All of the rows correspond with each other. So Sheet1!A2:A100 has its relevant data in Sheet2!A2:A100.

I have attached a very simple explanatory sheet.

I hope someone can help me.

Thanks,
Dan

Thanks, this works great.

I have just realised I forgot to add something. On sheet 1 I would like an overdue column so instead of pasting the amount into the correct week, if a payment was overdue (past the date when running macro) it will move the amount from the date column and into the overdue column.

Thanks,
Dan

Hi once agan thanks for your reply. I have made it simple with a few comments on the actual sheet. Cheers, Dan

Thanks once again, this works, would it be easy for me to add something to the code so that when it places the amount in overdue it deletes it from its original place under the date heading?

Also I have tried playing around with following but once again I am having no luck.

If I had a third page (sheet 3 for example) would it be possible to be able to take any row with a number in the overdue column and place that whole row into the third sheet?

Thanks in advance,

DWilkinson

Thanks this works perfectly and does exactly what I need it to do. If you do get chance to look at the row to sheet 3 that would be amazing.

I will then try and append macro and build it into my working document with hundreds of rows and column of data, I should hopefully be able to work out what to change in the macro etc.

Cheers,
DWilkinson

Hi

Once again great thanks for getting back to me with some great code. Unfortunately I had to revert back to the piece of code before the last post so it now doesn't include the copy to sheet 3 function.

However I have incorporated this into my workbook and worked out what values of the code to change to get it to work correctly. I have now been asked to alter this ever so slightly more.

I have attached a replica of my book but with only a few lines of de-sensitised data.

Working from this new attachment, what I now need to do is add 2 things, which I think are simple but I just spent a few hours trying to do but had no look.

1) The code currently takes the date from column 'M' and does its magic. I now need the code to take the date from column 'L' aswell as 'M'. It will be one or the other. There will never be a date in both.

2) The dates that run across the row 'A' are week start dates. On sheet 2 I have a cell 'G1' that displays the date of the Monday in the current week we are in. So this week it will display 13/10/14. I would like if possible for the macro after or before it sorts that data to read them dates and for any row with the header cell that is in the past to be deleted. It shouldn't delete the week we are currently in though hense the formula in sheet 2 'G1'. Reference that cell in the macro if you need to.

Once again thanks in advance and your help is very much appreciated.

Thanks,
Daniel W

That's great thankyou, just pop in a random date for testing purposes.

Thanks again,
Daniel

Hi

I messed up and forgot what I did. The macro can stay the same because the if you put a date in column l it will then appear in column m.

I will just need some assistance with the deleting of the columns with the date in the past.

Thanks,
Daniel

Yes - This is great thank you, I have decided to put some formulas underneath the data and across to each week. Is there a simple way that I could change the macro to only sort data up to ROW 8000?

Thanks,
DWilkinson

Thanks once again, I will try and finalise this document soon, and make sure I don't need anything else in it.

Thanks,
Dwilkinson

xladept

I notices that you helped with the formula on another post. I have attached the worksheet which is now fully working. I just have a slight query.

You wrote the macro that inserts a new row and uses the fill handle to bring don the formula from the row above.

Basically the company codes that are located from a9006 downwards are the same as the codes from a2 downwards, when I populate the sheet with the relevant data there will be around a 1000 rows of data each with a company code in column A. There might be 100 of the same and 75 of another for example. AEL001 x 100, AEL005 x 75.

My query is can a macro search column A from A2 --> A8000 and basically take all the company codes make just one copy of each different one and display them from cell A9006 downwards.

In the example I have attached you will see there are 5 x AEL005 company codes and 1 of each AEL001, AEL002 etc. So the list of company codes that starts in cell A9006 should go;
A9006=AEL001
A9007=AEL002
A9008=AEL003
A9009=AEL004
A9010=AEL005


The list starting from A2-->A8000 will be growing week by week. And the macro currently work by pressing add customer.

Thanks,
DWilkinson

Just column A is fine I already have the formula to sort the other data. So the codes listed in A9006 should be the coffee that are listed from A2 to A8000. If in a15 to a60 out had the same code then outwould only display thatcode once in the list that begins begins in. A9006.

Thanks,
DWilkinson

Third segment? Not following. Sorry,

Dan

Oh yes I get you. So yeah 1 the data then a sum of all the data then a sum for each company seperately. Thanks so Mich I will have a play later on and will see how I get on.Cheers Dan

Ok so this works great however when it inserts a new row and places the company code into the A column if it's possible would it be able to take the formula from the whole of the row above up and copy them down.

I will save in my master copy the formulas in row 9006 so every time it populates it will always pull the formula down from that. I haven't finished sorting the formula for this row so if you can do it then just test using a really simple formula just so we can see it incrementing down each row as it adds the company codes.

Thanks,
Dan

Sorry mate but that's doing the same thing.

It seems to choose the customer row and then insert so each time it inserts a row the row which started directly under the customer cell (with the formulas in) is being pushed down.

I can only assume that is why there is no code being copied down either as this is done last and the code Is at the bottom?

Cheers,
Dan

I think I fixed it, I changed the 4th line to .row - 0 instead of 1 that seemed to do the trick.

Is there a way to do a formula that does the following;

Take the column it's in a do a sum of row 9006 to the cell directly above the formula. Use row b as an example.


Cheers,
Dan

Hey sorry was late when I wrote that and I was getting confused.

Basically I meant to put that the formula in B9006 will return a figure.
At the bottom of the rows that you have got to insert with the company code there is a ROW with totals in so how could I do a formula that with be in the totals row to basically go sum B9006 and all the way down until it gets back to the cell where the formula in or the one above it as that will be the last cell with data in.

My problem is the fact that list will be changing from week to week so I cant just write a simple sum formula as I don't know what the range will be.

Cheers,
Dan

xladept... thank you

Hey

Thankyou - there is no formula in a column, the formula in b, and d column doesn't have to be there I just thought by putting forumula in the whole row without any gaps to see if it would work.

The formula in E9006 should be

Please Login or Register  to view this content.

The formula in F9006 should be

Please Login or Register  to view this content.

The formula in E9007 should be

Please Login or Register  to view this content.

The formula in F9007 should be

Please Login or Register  to view this content.

It should carry on down and across like that

Thanks,
Dan

Cheers Buddy

I have one last adaptation for this then I am done, with project anyway.

I need a new macro that takes the company code from cell A9006 and display whatever the column B value is.

So if AEL001 is in A9006 then in B9006 it will display as Asda. And so on. This will need to work it's way down from row 9006 onwards until there is no data left.

Thanks in advance
Dan