Transferring data from master book to individual books based on date and employee #?

[marthacb]

Hi all, I have a master workbook for 5 employees and the profit, quantity, etc. of products they’ve sold every day for every month. I keep individual workbooks for all of my employees, every one containing 2 worksheets (the current year and next year). Each worksheet also has data in 12 blocks, laid out side-by-side, corresponding to months.

Every day, I enter about 40 sales into the master sheet. I need to find a way to disperse these sales into the correct employee’s workbook and into the correct month/year block.

I’m thinking I need 12 if statements for every month x 2 years = 24 statements x 5 employees = 120 statements. Is there a way to reduce this or structure it differently? I’m a VBA novice so I’m a little uncertain about how to proceed with this. Any help would be greatly appreciated.

 

[marthacb]

Bump

 

[AlexandraT]

Not completely understanding how you want it done but if you can put all your data in a pivot table and then arrange it to look like you want, you can use the show filter pages option to get individual sheets for all employees. A simple enough macro can then be used to save them in individual workbooks. A sample workbook might help in understanding what you need.

Alex

 

[marthacb]

Ah, sorry I wasn't clear enough. First time posting, haha. This is today's sheet in my master workbook:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	6/17/2015
2	Account	Order #	Date	Empl #	First Name	Last Name	Card Number	Street Address	State	City	Zip	Prod #	Price	Quantity	Total	Status	Notes

<tbody>
</tbody>

Today​

and this is the employee workbook:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	May 2015							June 2015							July 2015
2	Customer	Account	Date	Total	Quantity	Prod #		Customer	Account	Date	Total	Quantity	Prod #		Customer	Account	Date	Total	Quantity	Prod #

<tbody>
</tbody>

2015

​

Based on the month, employee number, and year, I would like to be able to move data from the master sheet to the employee workbooks without copying and pasting manually. Sometimes I get orders for next month or next year and I would like to move the information accordingly.

 

[marthacb]

Bump

 

[AlexandraT]

I still think organizing the data with a pivot table is your best bet. You can then make separate sheets for each employee which can be saved individually. Ideally, a macro would be best but I can't really help with that. If you post a sample workbook I can organize the data for you in a pivot and maybe think of a rudimentary macro to save sheets individually in their own workbooks.

Alex

 

[pmich]

VBA Code can also be written.

Say in Master Workbook in D3 you have posted Empl #.

Then, Account, Date, Total, Quantity, Prod # which are of this Employee, posted in Row 3 in Master Workbook can be retrieved.

Do you have the Empl # as the name of the Employee Workbook or do you have the Empl # as name of the sheet of the workbook of the Employee?

Based on your reply, the workbook of the Employee can be opened and the above data can be posted in the chosen year sheet of the Employee in the corresponding Month Block based on the date retrieved from Row 3 in Master Workbook.

What else do you expect to retrieve and where do you expect them to be posted?

A sample data of Master Workbook as well as of the Employee Workbook will throw more light.

What all data to be pulled out and where to post them may be shown in your sample data. This will help to reply you meaningfully.