Paste location depending on condition

[Animeonmymind]

Hello Everybody,


I wanted to ask you a question about an excel file i've been trying to improve.
So short summary about what it needs to do:

---
This file is being used to keep track about all administered blood transfusions in the hospital.
Every month or sometimes every week, we generate a report from our laboratorium program that tell us "for that month, these were the products that were checked out to these patients".

After we get that file we put it in our Excel file, add a few columns and then copy it to an excel sheet with every transfusion that has been administered over the span of a few years.
---



Now I have been optimizing that file so that it automatically adds the columns from other worksheets but I was wondering if it were possible to write a VBA function that will do the following:


1. Check if the generated rapport has already been pasted in the Worksheet 'Total'.
I've added a code in every generated rapport at the beginning and ending of the data rows called (B)MONTHYEAR(B) and (E)MONTHYEAR(E) so it can be cross checked with the worksheet 'Total'

Example

DATA DATA DATA DATA DATA DATA (B)DECEMBER2013(B)
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA (E)DECEMBER2013(E)
DATA DATA DATA DATA DATA DATA (B)JANUARI2014(E)
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA
DATA DATA DATA DATA DATA DATA (E)JANUARY2014(E)
DATA DATA DATA DATA DATA DATA (B)FEBRUARY2014(B)


2. Paste the newly generated worksheet over the old one AND keep the cells not part of the current rapport intact. (so it needs to add new rows so it won't replace the following month).

Example: If the new DECEMBER rapport has 700 rows and the old DECEMBER rapport in the worksheet 'Total' only has 500, it should paste over the first 500 and expand the other 200 so it won't copy over JANUARI.


3. If it hasn't been pasted yet, the VBA should paste it on the next empty row.

Example: After (E)FEBRUARY2014(E) should come (B)MARCH2014(B).



I have a good idea of how it should work in my mind, I just can't seem to write to code for it. I think it should go like this:

1. Select from (B)MONTHYEAR(B) to (E)MONTHYEAR)
2. Change to worksheet 'Total'
3. Check if in column M the anchor from the new rapport occurs (B) & (E)
4. IF it does not occur, paste in the next empty cell
5. IF it does occur, paste beginning from (B), if it comes acros another (B) it should add rows instead of replacing that information.




If you guys think there is a better way of handeling that problem I would be happy to hear. Thanks for your time!

 

[Animeonmymind]

Anybody? I'm really breaking my head on this and it's driving me mad not being able to code it.