Help with copying data from master sheet to multiple sheets based on month

Try

Please Login or Register  to view this content.

Originally Posted by jindon

Try

Please Login or Register  to view this content.

thanks Jindon, but unfortunately nothing filters (and therefore nothing copies)?

Then upload a workbook with the small amount of data.

Impossible to test without the data.

ok, the sample workbook that I uploaded now contains several dates. This is all that is needed to test.

Originally Posted by Trebor76

Can I ask why do want 12 identical sheets?

Need a separate sheet for each month (as well as the summary sheet).

It is filtering properly and pate to the appropriate sheet respectively here.

I get 12/1/19 and 15/1/19 copied to each sheet and nothing else??

OK, perhaps the dates.

In A6, value in formula bar shows as 2019/1/12, means Jan 12 2019.

Is that the same?

Yes, it must have something to do with the dates and settings. On mine, the formula bar shows as 12/01/2019. Perhaps the macro can be amended or do the dates need to be changed in some way?

Can you just try using CLng function?

Please Login or Register  to view this content.

I get an error: Autofilter method of Range class failed???

OOps..

Please Login or Register  to view this content.

Originally Posted by jindon

OOps..

Please Login or Register  to view this content.

same result unfortunately. 12/1/19 and 15/1/19 copy to each sheet but nothing else??

Here's the AdvncedFiltrer version and hope this works.

Please Login or Register  to view this content.

Yes! Works perfectly! Thank you!

You are welcome and thanks for the rep.

Date is always a problem...

Originally Posted by jindon

Here's the AdvncedFiltrer version and hope this works.

Please Login or Register  to view this content.

This has been working really well for me. But since the start of the new year, I need to filter and copy based on dates in the financial year. So for the tabs Jul-Dec, these need to be dates in 2019, then for tabs in Jan-Jun, these need to be for dates in 2020. Any idea how this code can be amended to reflect this?

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by jindon

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks jindon. I tried that but unfortunately no records copy at all to any of the tabs?

Ahhh, perhaps

Please Login or Register  to view this content.

thanks jindon. This still copies all records though, e.g. if I have a date 30/6/19, it copies to the Jun tab, but this should not happen as it is Jun 2019 (the previous year)? Is there a way it will copy all current years records to Jan-Jun that are relevant, and the previous years to Jul-Dec?

For example:
12/8/2019 (should copy to the Aug tab and currently it does)
15/6/2020 (should copy to the Jun tab but currently doesn't)
30/6/19 (should not copy at all as the Jun tab would be for records in June 2020 but it currently copies over)
25/2/20 (should copy to the Feb tab as it is in 2020 but currently doesn't copy at all)
2/5/19 (should not copy at all as the May tab would be for records in May 2020 but it currently copies over)

See if this works,

Please Login or Register  to view this content.

Originally Posted by jindon

See if this works,

Please Login or Register  to view this content.

thanks jindon, it is almost right I think.

15/6/2020 does not copy over to the June tab but 30/6/2019 does when it shouldn't (as this is the previous year). Perhaps there is an issue with June only?

Then change 5 to 6,

Please Login or Register  to view this content.

Originally Posted by jindon

Then change 5 to 6,

Please Login or Register  to view this content.

thanks jindon! I will do some more testing but this looks like it is working ok

As we have moved into the new financial year, I am having trouble copying the correct records over now. I only want records that occur in the current Financial year (1 Jul 20 - 30 Jun 21).

For example:
12/6/2020 (should not copy as this occurred in the previous financial year)
2/7/20 (should copy to the Jul tab as it is in the 2020 Financial Year but currently doesn't copy at all)

Is there a way the above can be adjusted to solve for this?

Try change to

Please Login or Register  to view this content.

perfect, thanks jindon! Will this work for future financial years? That is, in 12 months time, will it copy records in that financial year (1 Jul 21 - 30 Jun 22) and so on for future years? Or will it need adjusting each year?

It should...

ok thanks again!

Originally Posted by jindon

It should...

I know this is an old post, but this is my first attempt to copy records for this financial year (1 Jul 21 - 30 Jun 22) and unfortunately the above does not return any records.

jindon - any thoughts on this?

Thanks!

If you upload a workbook that you are using and the code that does not work, it would help.

ok,here is a sample with just the raw dates. Each of the dates should copy to the respective "Month" tab based on the date.

ok thanks jindon! That works great! I will test on my master workbook and let you know how it goes. Do you think this adjustment will work for future years, or is it possible that I will run into a similar issue for 2022 / 23?

It should work with current financial year. I hope.

ok, thanks again.

Originally Posted by jindon

It should work with current financial year. I hope.

Again, I know this is an old post, but I have gone into the workbook and tried to copy records for this financial year (1 Jul 22 - 30 Jun 23) and unfortunately it copies only records for 2021. But I don't want these. It should copy records for the current financial year and ignore previous records in the previous financial year.

Sample attached.

jindon - any thoughts on this?

Thanks!

Not really sure though

Please Login or Register  to view this content.

thank you, I will give that a try.

change to

Please Login or Register  to view this content.

thank you, this is great. I had a feeling there was another For...Next Loop in there but I couldn't quite figure it out.

this works well, except that it copies the data for Jul-22 twice? I am not sure how to resolve as the formula specifies month < 7?

change 7 to 6

Please Login or Register  to view this content.

Ah, that makes sense. Thanks again

I know it has been sometime since I replied to this post, but with the new year now, I am experiencing issues again.

With the sample attached above, I get a 'subscript out of range' error on this line:

Please Login or Register  to view this content.

The workbook will always contain a rolling 15 month period from April the previous year to June of the current year.

It seems I have issues when:

1. The year rolls over to the new year and
2. The financial year changes from Jul to Jun each year.

jiindon - can you please take a look again for me?

1) Copy any one month sheet that is blank (e.g. MAY-22) and name it as a template then make it hidden and save the workbook.

2) code

Please Login or Register  to view this content.

ok thanks.

But now it adds more sheets (the sheets from Apr the previous year to Jun of the current year are already present), but it added sheets for the next 12 months.

It also didn't copy over properly? See sample attached.

You know I totally forgot the requirements, and I don't want to read all through this thread, so

Can you repeat your requitements in detail ?

OK, please completely forget about post #51.
See if this works

Please Login or Register  to view this content.

ok thank you.

It only copied over a couple of values and these were for 2023 and were copied into sheets for 2022. See sample attached.

Ok, one more change in the formula

Please Login or Register  to view this content.

need to be

Please Login or Register  to view this content.

The reason it didn't work in new year was the code was looking the Current year, but now it is referring to B6 in Summary sheet.
So remember the year in B6 must be in current financial year.

ok, thank you.

This copied most values, but the values for Jul 23 and Aug 23 were copied into the sheets for Jul-22 and Aug-22.

I assume this is to do with the year in B6 being in current financial year?

This value in B6 will almost always be in Apr or possibly May of the previous FY.

Is there a way to adjust the code to account for this?

OOps, one more,,,

Please Login or Register  to view this content.

need to be

Please Login or Register  to view this content.

ok this appears to be working ok.

Will this still work if the value in B6 IS NOT in the current FY?

If you prepare all the worksheets regarding that year, it will.

That's what I had tried to create new set of worksheets, if financial year changes (failed anyway though).

ok, thank you. I will do some testing and see if it is ok.

Originally Posted by maym

Will this still work if the value in B6 IS NOT in the current FY?

Originally Posted by jindon

That's what I had tried to create new set of worksheets, if financial year changes (failed anyway though).

When the year in B6 changes and if you run the code, it will create a new set of worksheets and sort them.
Sheet "template" is added and hidden.

Please Login or Register  to view this content.