Copy data from one worksheet to another based on month/year

Hi,

Thank you for taking the time to look at this for me.
I have followed the instructions and the code runs and says complete yet nothing appears in Jan or Feb. Nothing appears to happen at all.
I have reattached the macro-enabled workbook with the code added.

Andrew

You could do this with a few formulae - are you interested in a formula-based solution?

Pete

Hi Pete,

I would try any solution so please let me know what you have in mind.
Never used VBA before however Alan's solution wasn't too hard but I couldn't get it to work.
I have tried VLOOKUP etc but no joy. I am completely stumped.

thank you.

Okay, first of all, put this formula in E12 of the January sheet:

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

and then you can copy this formula into E12 of the February sheet. The formula will give you the sheet name, and so if you copy the Feb sheet and rename it as March_2020 that will be shown automatically, so it will make it easier for you to set up the other months.

I've used a helper column in the Caseload sheet, by inserting a new column A and putting this formula in A12:

=IF(F12="","-",TEXT(F12,"mmmyy")&"_"&COUNTIFS(F$12:F12,">="&EOMONTH(F12,-1),F$12:F12,"<="&EOMONTH(F12,0)))

You can copy this down by double-clicking the fill handle (the small black square in the bottom right corner of the cursor). This formula will set up a unique code for each record, based on the booking date in column F. You can hide this column if you want the sheet to look the same as before.

Then in the January sheet you can use this formula in cell B18:

=IFERROR(VLOOKUP(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A:$E,COLUMNS($A:C),0),"")

which can be copied across into C18 and D18. You might need to format D18 as a date if it shows just a number, and then you can copy those 3 formulae down as far as you need them. You can also copy them into the February sheet, and you should see that the January sheet contains 3 records and the Feb sheet shows 2 records, as expected.

You can use the February sheet as a template to be copied for the other months - just Ctrl-drag the sheet tab along the bottom of the file window, then rename it as appropriate.

Hope this helps.

Pete

Hi Pete,

Thank you for that, it worked like a charm! I really appreciate your time helping me to get this done.

Andrew

Hi Pete,

Sorry to bother you again, your fix has worked great but I have now created myself another problem...Previously as I had all the women on caseload and all of them also pulling through to the one month, I had set up formulas to count cells depending on the contents with names (or lack of them) in a row of cells cells. Now the women are on different monthly sheets depending when they were booked so my formulas are useless. Actually, they work (sort of but not safely) for aug_2020 and in Sept_20 I can't specify the location or identifier anymore which I presume I would also need for any month. I have been looking to use an IF with the result of your formula in column A of caseload to identify which row to use in my original formulas but I can't get it to work.

On the monthly sheets, I am capturing in column H, the total number of contacts by adding up cells K:X on caseload where there is a name in the cell ignoring blanks. In column I, capturing the number of times the lead (caseload column D/ month column C) has her name in cells K:X on caseload. Finally in column J, capturing the number of times anyone of the other 7 (not the lead) have their names in cells K:X on caseload again ignoring blanks.

I have uploaded the current file...thank you

Hi Andrew,

I've only just seen this, but as it is getting a bit late (and football is on TV later on), I won't be able to look at it tonight.

I'll take a look tomorrow.

Hope this helps.

Pete

P.S. Thanks for the rep, by the way.

Originally Posted by Pete_UK

Hi Andrew,

I've only just seen this, but as it is getting a bit late (and football is on TV later on), I won't be able to look at it tonight.

I'll take a look tomorrow.

Hope this helps.

Pete

There is no urgency whatsoever. Enjoy the football and whenever you get a chance to look that would be great!

Thanks, Andrew

Hello again Andrew.

Instead of using a fixed range, (e.g. K12:X12, which is then copied down), you need to determine which row in the Caseload sheet applies to the row in the monthly sheets. You can do that using this expression:

... INDEX(Caseload!$K$12:$X$186,MATCH(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A$12:$A$186,0),0) ...

and you will see this expression in many of the formulae that follow (I'll colour it red, for clarity). Also, you can use COUNTA to count the number of non-blank cells, so the formula in H18 of the monthly sheets will become:

=IF(B18="","",IF(COUNTA(INDEX(Caseload!$K$12:$X$186,MATCH(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A$12:$A$186,0),0))=0,"",COUNTA(INDEX(Caseload!$K$12:$X$186,MATCH(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A$12:$A$186,0),0))))

No need to enter this as an array formula with CSE.

The formula in I18 will be:

=IF(C18="","",COUNTIF(INDEX(Caseload!$K$12:$X$186,MATCH(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A$12:$A$186,0),0),C18&""))

and if understand you correctly, the formula in J18 is just the difference between these two, so it can be simplified as:

=IF(C18="","",H18-I18)

Copy these down as required.

Hope this helps.

Pete

Further to this, you can simplify the formula in H18 of the monthly sheets to this:

=IF(B18="","",IFERROR(1/(1/COUNTA(INDEX(Caseload!$K$12:$X$186,MATCH(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A$12:$A$186,0),0))),""))

then copy it down.

Hope this helps.

Pete

Originally Posted by Pete_UK

Further to this, you can simplify the formula in H18 of the monthly sheets to this:

=IF(B18="","",IFERROR(1/(1/COUNTA(INDEX(Caseload!$K$12:$X$186,MATCH(LEFT($E$12,3)&RIGHT($E$12,2)&"_"&ROWS($1:1),Caseload!$A$12:$A$186,0),0))),""))

then copy it down.

Hope this helps.

Pete

Hi Pete,

Thank you for all your help and the time spent looking at this and replying! The formulas work exactly as expected and I have learnt such a great deal from your input.

Andrew

Originally Posted by alansidman

My code works, but you have values in cells through row 192. Delete the formulas in Column B of the target sheets. Your formulas in that column is causing the code to look at the last row with data which is 192. Scroll down to see the results.

Ah yes I see it now. I have learnt a lot looking at, and trying to understand your code. Thank you for your help and input, much appreciated!

Andrew

Originally Posted by andynw1

Hi Pete,

Thank you for all your help and the time spent looking at this and replying! The formulas work exactly as expected and I have learnt such a great deal from your input.

Andrew

Glad to help, Andrew. Thanks for marking the thread as Solved.

Pete