VBA macro for Vlookup,pivot and separate files

If separate sheets will suffice rather than separate files, this is fairly simple to achieve without VBA.

1) Convert your data to a Table - select any cell within the raw data and press Ctrl + T to create Table. This should show the data area to be converted and should just need OK.  When new entries are added to the table the formulas in L to P will copy down automatically.

2) For your lookups change the formula to look at whole columns on lookup sheet, any additions are then already included.

3) In your Pivot select Sales Unit as a Report Filter/Page Value, top left pane at the bottom of the Fields List navigator pane. When the Pivot has the Page Filter set go to the Pivot Table Tools Ribbon and select Options tab. At the left hand end there is an Options button with a dropdown. Don't click the Options button as this just opens the Table Options window. Select the dropdown and choose "Show Report Filter Pages...". This opens a Window and will show a list of item for which you have Report Pages, it should be just Sales Unit. Click OK and a new sheet will be created with a Report for each Sales Unit. If you then want each of these as a separate file, use the Right Click menu on the tab to Move or Copy.

Thanks
Rob H

Hi Rob, thank you for quick reply. while the information is sensitive and cannot be shared with others I was looking for an individual files and wouldn't be a macro be helpful for cutshort all other steps as well.
thank you very much again

Steps 1 & 2 would be one off tasks and then add new data as required.

A macro could be used to refresh the pivot and create the individual files.

Please find the attached workbook with the code and click the button on Raw Data Sheet to run the code to see if this is what you were trying to achieve.
file.xlsm

Hi Neeraj,

Thank you very much. works perfect. just a couple of more changes

1. In pivot how can I uncheck subtotal for each acronym
2. Based on the sales unit a separate file have to be created with pivot with Sales unit name as filename(Column M)

Thanks again

Okay check the attached.
The code will save the files for each Sales Unit at the same location where the macro workbook is saved.
file.xlsm

Thank you again. sorry not making it very clear. Would need rawdata for individual Sales Unit along with pivot.

attached is the sample outputfile
GK00G0K-_Africa.xlsx

Hmm. Please see the attached.
file.xlsm

its working brilliant what i am looking for. one last question when i do a vlookup for a formula it is giving #N/A error how do I paste special as values after the formula is copied till last row example if sales unit is only number it is not picking the vlookup and I am trying to create files based on "Sales Unit Name"

thank you

Please find the attached to see if this is what you were talking about and trying to do.
file.xlsm

if i have to change filenames to sales unit names how can I change and i am get the pivot in the final file but raw data i get only heading

Sir I am getting a debug error at line

fileName = WorksheetFunction.Index(sws.Range("M:M"), WorksheetFunction.Match(str(i), sws.Range("L:L"), 0))

and getting all pivots in one file

It shouldn't happen. All the Sales Units must be available in col. L if the value exits in the page filter.
When you debug the code, hover your mouse over str(i) inside the Match function, what value populates then? And check if the sheet for that Sales Unit is created with Pivot Table.

It is hard to debug the code without having the workbook. If possible please upload the workbook with data in question.

you have been providing brilliant solutions. thank you so much. really appreciate. thanks again

You're welcome Kiran! Glad to help.

Thanks a lot. I could which only i could provide you more points.