Need macro to copy data from 1 sheet to multiple sheets based on certain criteria

Hi

Have you contemplated the number of worksheets required ie 1 sheet per employee * 26 pays per year for 3 years, which could be upwards of 200. This could become unwieldy and maybe there is a more efficient way.
In the meantime I will continue to find a solution for you.

There would never be that many. This is to make corrections on a very small scale. The one I'm doing now is the largest I'll probably ever do and it won't be 50 tabs. I can always break into two files if necessary. One tab for each pay period/person.
I realized last night that there will be some instances where I will only need one line item and not two. I would just need the data with the "earnings account" and wouldn't need the 2nd line item with the "correct earnings account". Is there a way that you can build that part in so that I could block out the instructions for the 2nd line if necessary? I can't tell you how much I appreciate your help with this!!!! Thank you!

Hi Alf,
Thank you so much - works great except for 1 thing.... It's putting each line item on it's own page when the name AND pay end date are the same (like rows 17 & 18, 20 & 21, 2 3& 24), they should go onto the same form. We could add a column on the data tab to make a formula to test that if the name and pay end date don't change from the row above then it should go on the same form. Do you think you can add the logic to make that happen? I really appreciate it. Thank you!!!!!

It's putting each line item on it's own page when the name AND pay end date are the same (like rows 17 & 18, 20 & 21, 2 3& 24),

Not sure I understand that problem. Looking at the data in your uploaded file I see

Row 15 smith, John 67891 1 2017-08-12 PHN
Row 16 smith, John 67891 1 2017-08-26 PHN
Row 17 smith, John 67891 1 2017-08-26 Reg

so as far as I see row 15 should generate a a sheet and since row 16 and 17 contains the same name and date both these rows should be added to the same sheet which my macro does.

Row 18 Smith, Susie 98765 0 2017-10-21 Reg
Row 19 Smith, Susie 98765 0 2017-08-31 OVT
Row 20 Smith, Susie 98765 0 2017-08-31 PHN

again as I see it you should have two sheets for Smith, Susie. One with the data from 2017-10-21 and the other containing the two sets of data from the 2017-08-31. This is the result the macro produces.

The only thing that is a bit strange is the format setting on the template sheet cell A5 as this shows date in an unusual format i.e. 07-31-17 (mm-dd-yy) but in the formula field it is shown as 2017-07-31
(Scandinavian date format) but as macro makes a copy of the template file all settings there are transferred to created sheets.

As for the logic in sheet adding macro first copies all names from data sheet to an new sheet called "analyze", the list of names are then edited by excluding duplicates.

This edited list is then used at a criteria (one name at a time) setting an autofilter. The autofiltered result are copied to the "analyze" sheet and then the macro builds a string with the date + "_" + name.

Macro will then try to activate a sheet with that string name. If sheet exist result are copied to activated sheet and if sheet don't exist macro will add one with that string name and result copied to it.

I'm sorry I can't help you but as I don't see you problem I don't know what to suggest.

Why don't you upload a file showing the results after running the macro and adding comments to show where things have gone wrong.

Alf

I think I referenced the wrong row numbers in the last message I sent you (I may have been looking at a different version of the file). I've uploaded two filed here. "PCT download report_mod after macro has run" shows you what I got after I executed the macro. As you can see, I only see one line item from the data sheet per sheet generated. I also uploaded a "manual example of what macro should produce". In this workbook, I sorted the data by name, then pay end date, and then inserted two columns (A & B), where B puts the name and date together for a test name, and A decides whether the line item should go on the same sheet as the record before it, or a new sheet. In this care, there would be 12 sheets created, with 3 sets of line items (rows 14 & 15, rows 21 & 22, and 23 & 24) would be on sheets 4, 10 & 11, respectively. I created all the sheets, but didn't worry about having the correct data on the lower part of the sheets.
Please let me know if this information is helpful.

As far as the date is concerned... I am in the United States, and the date format I'm using is mm/dd/yyyy. I'm guessing you're using the European format which is dd/mm/yyyy maybe??? Maybe you see my dates in your format? But the format I've intended for everywhere on my worksheets is
dd/mm/yyyy. Hope that helps.... Thanks again.

As far as I've understood your problem you wanted to create separate sheets from the data based on name and pay end date.

If there was a name with the same date twice then all information this information should be put on the same sheet.

For instance Doe, Jane has pay end date 31-07-2017 twice and as each line generate two lines in the sheet one for the 12000289-2200-91299 earnings account and one for the
12000287-3002-90200 correct earnings account there will be four lines of information under Earnings Code and Account.

This is what the macro is set up to do and as far as I can see this it does. The sheet naming convention is based on the fact that this information is found in the data sheet and if a name and a date pops up father down then it will be placed in the same sheet as previous result if it has the same name as well as the same date.

Take the example with Doe, Jane date 31-07-2017. It will not matter if you separate them or where you place these two lines in the data sheet. Macro will find them and add both to the same sheet (31-07-2017_Doe, Jane)

So out of four lines with data for Doe, Jane this will generate 3 sheets in the file
named 31-07-2017_Doe, Jane. 30-09-2017_Doe, Jane and 31-10-2017_Doe, Jane

The Scandinavia date format by the way is YYYY-MM-DD but as Excel adapts to the environment it is in this should be no problem.

Have a last go and try running the macro again and see if this result makes more sense to you and that it is what you was looking for.

I've changed the format on the template cell A5 so hopefully it will now give you DD-MM-YYY format.

The other change in the macro is that in starts by clearing all the sheets in the file except "data" and "Template" before rebuilding all the sheets.

Alf

The sheet names are automatically generated - what I uploaded is exactly what I got after running the macro. Here is what I got when I ran it again. Same problem. I wonder why it acts so differently in the different environments.
Would you be willing to try the logic based on the sheet # in column A like in the file "manual example of what macro should produce"? Look at the data tab in columns A & B. I can easily add that to my data sheet. See attached file. Thank you!!!!

Ok I think I solved the problem. The normal format for US date is 8/31/2017 but in sheet name "/" is a forbidden character so the macro makes this Template(2), Template(3) and so forth. Even changing it to 31-08-2017 will not fix this problem as result is still Template(2), Template(3) and so forth.

What I did was to change datum format in the D column (data sheet) to numbers so 8/31/2017 was transformed to 42978.00 and then the macro did run ok.

So you got funny sheet name i.e. "42978_Black, John" but if you check cell A5 in that sheet you see the date as 8/31/2017 so you know the proper date.

Will have a think about it, perhaps if date is changed to 31-08-2017 and defined as text then macro will work.

So now I'll try to change back my computer to the Scandinavian setting.

Alf

Judy pretty please with sugar on !!!!!! Delete you mail address as soon as possible!!!!!

Never ever ever put you email address on an open forum you may be drowned in spam.

If you wish to contact me, not recommended as problems should be handled in forum for the benefit of all you may send me a PM through the forum.

Alf

I deleted my email from the post above. Thank you for that advise. I'm relatively new to the forums.
I don't see that you attached a new file after you tried changing the date format. Can you please try again? Thanks!

Yes I did forget that. Just euphoric after solving this problem so I forgot. But there must be a way to use date as a sheet name. Have tried different settings but most of the time
these change the visual layout of the date. Looking at the formula field it still shows mm/dd/yyyy even if visual (cell value) shows dd-mm-yyyy

Alf

Getting closer!!!!! Looks like it's working properly except for the way the date is acting. What about before the macro creates the tabs another column is added with a copy of the dates in the dd/mm/yyyy format. After the macro runs that column can be copied back over the column showing the dates not as a date. If we can't get the date on the sheet name to present properly can it be left off? Or what about creating a column with the date hard coded as mm.dd.yyyy so there are no "/" ?
Again, thank you so much!

Like professor Higgins I feel I can now say "My God I got it!"

Well the quote is not really like that but it was the best I could think of. File is also tested in an US environment and runs fine. The crux of the matter was the formula I added to the
macro i.e. =TEXT(cell,"mm-dd-yyyy")

This will give you date in the format mm-dd-yyyy, and if you wish for another format just change the line

Please Login or Register  to view this content.

to the format you wish for as long as there are no forbidden characters in it.

So just click on the macro "ArrData" and do a rerun to test and you don't need to delete all the result sheets, the macro will do that.

Alf

Alf - you are the best! Thank you so so much!!! Is there anywhere I can write a review about you?
Thanks again and take care.

Forgot to mention... I think a walkthrough would be great if you could do that.

I am not seeing where I mark this "solved".
Thank you again!

Page 1, top right "Thread Tools", drop-down box. See image

Judy.jpg

Alf

So here is the promised walk-through of the macro used in your file, hopefully this makes sense to you or you could be more confused but now on a much higher level

Alf

Thanks again. Take care.