Hi guys,
here`s the scene: I work for a work agency and I have a set of data containing name, salary etc. which belong to workers for different companies who use our outsourcing services. there`s about 50 of them and after each months end I need to provide a workbook where each company account has a separate sheet with data related to the workers.
I have a pivot table with the data I need to fill in each of the sheets ("pivot") and also another pivot table which serves as a list of our internal numbers of the company accounts used in the current month ("DED")
I have figured out a code which will pick up the individual company account numbers and create a new sheet for each of them
What I can`t figure out is how to copy relevant data for each account from the "pivot" sheet, ie. go to the pivot, search for the account number (which is in rows) and copy all the workers data which belong to the account (the number of workers - the number of rows is different every time, the number of columns is 6 - there are usually empty cells within the selected range) into the relevant account tab where I have a template which needs filled in and the pasting should start in cell B9.
I can add empty lines between each of the account data to cut the pivot into small pivots thus making it possible to use the xlDown selection possible.
Here`s the code I`m using for the part that I have figured out - I suppose it`s best to add more code into this loop rather than making it a separate loop but whatever works is much appreciated!
here`s the scene: I work for a work agency and I have a set of data containing name, salary etc. which belong to workers for different companies who use our outsourcing services. there`s about 50 of them and after each months end I need to provide a workbook where each company account has a separate sheet with data related to the workers.
I have a pivot table with the data I need to fill in each of the sheets ("pivot") and also another pivot table which serves as a list of our internal numbers of the company accounts used in the current month ("DED")
I have figured out a code which will pick up the individual company account numbers and create a new sheet for each of them
What I can`t figure out is how to copy relevant data for each account from the "pivot" sheet, ie. go to the pivot, search for the account number (which is in rows) and copy all the workers data which belong to the account (the number of workers - the number of rows is different every time, the number of columns is 6 - there are usually empty cells within the selected range) into the relevant account tab where I have a template which needs filled in and the pasting should start in cell B9.
I can add empty lines between each of the account data to cut the pivot into small pivots thus making it possible to use the xlDown selection possible.
Here`s the code I`m using for the part that I have figured out - I suppose it`s best to add more code into this loop rather than making it a separate loop but whatever works is much appreciated!
VBA Code:
|