VBA: Copy / Paste Based on Column Criteria Multiple Sheets

[skoobi]

Hi All,

After spending a good part of the last 2 weeks trying many ideas and solutions i seem no closer to an answer.

What i am trying to achieve is this:

I have a workbook and in the workbook i have sheets named Properties, March 2018, April 2018 so on till December.

In the properties sheet in row 3 i have the following headers

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="width: 166, align: center"]A
[/TD]
[TD="width: 269, align: center"]B[/TD]
[TD="width: 123, align: center"]C
[/TD]
[TD="width: 113, align: center"]D[/TD]
[TD="width: 113, align: center"]E[/TD]
[TD="width: 113, align: center"]F[/TD]
[TD="width: 113, align: center"]G[/TD]
[/TR]
[TR]
[TD="width: 166, align: center"]Property Number[/TD]
[TD="width: 269, align: center"]Tenants Name[/TD]
[TD="width: 123, align: center"]Contact[/TD]
[TD="width: 113, align: center"]Rent per week[/TD]
[TD="width: 113, align: center"]Paid up to[/TD]
[TD="width: 113, align: center"]Date Paid[/TD]
[TD="width: 113, align: center"]Amount Paid[/TD]
[/TR]
</tbody>[/TABLE]

In rows 4 to 20 i have the property address and details.

In Sheet March 2018, April 2018 through till December, I have the sheet set out as follows more or less like a calendar
Column A & B Have Property Name & Tenants Name like Properties Sheet but they start on row 3

In Rows 1 & 2 and Columns C to AG i have the following - these are date formats they go right through to 30/31 for days of the month but custom so one is numbers the other is The Day
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="width: 29, align: center"]2[/TD]
[TD="width: 29, align: center"]3[/TD]
[TD="width: 29, align: center"]4[/TD]
[TD="width: 29, align: center"]5[/TD]
[TD="width: 29, align: center"]6
[/TD]
[TD="width: 29, align: center"]7[/TD]
[TD="width: 29, align: center"]8[/TD]
[TD="width: 29, align: center"]9[/TD]
[TD="width: 29, align: center"]10[/TD]
[TD="width: 29, align: center"]11[/TD]
[TD="width: 29, align: center"]12[/TD]
[TD="width: 29, align: center"]13[/TD]
[TD="width: 29, align: center"]14[/TD]
[TD="width: 29, align: center"]15[/TD]
[/TR]
[TR]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[/TR]
</tbody>[/TABLE]

Now what i want to do, i may be in over my head here but maybe someone might have a solution.

Each time a tenant comes in to pay i want to be able to just change the Date Paid & Amount Paid cell on the Properties Sheet to the date they paid and the amount they paid and once those 2 cells have been changed then i want it to automatically copy the amount they paid and find the date column on the Month Sheets that matches to the row of the tenant and paste the value of amount paid into the date column.

Hopefully i can just have it so we only need to change the 2 cells on the Properties Sheet each time they come in to pay and it will continually update the Month sheets until the end of the year and then start a fresh book next year. So it needs to copy the amount paid and use the date it was paid on in column F to find the date in the rest of the sheets and paste the amount that was paid to the day it was paid.

Hopefully this can be done with VBA as i haven't really found a direct answer to what i am trying to achieve.

Thanks again all.

 

[skoobi]

Bump anyone?

If you need more info please let me know