Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

VBA macro for Vlookup,pivot and separate files

Dear experts i have some tedious task of creating multiple files below is what I am looking for some automation

Column A to K is the Input file in Raw Data tab
Step1: Column L have to get updated by picking all the information before"." of Column J
Step2:  Column M have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Step3: Column N have to get updated by picking all the information after"." of Column J
Step4:  Column O have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Step5:  Column P have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated

Once the Raw data sheet is ready it have to create a Pivot as shown in the example

and create separate files based on the sales unit name


Thanks a ton in advance!!!
file.xlsx
0
Nirvana
Asked:
Nirvana
  • 8
  • 7
  • 2
1 Solution
 
Rob HensonFinance AnalystCommented:
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
2
 
NirvanamanagerAuthor Commented:
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
0
 
Rob HensonFinance AnalystCommented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
 
NirvanamanagerAuthor Commented:
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
file.jpg2. Based on the sales unit a separate file have to be created with pivot with Sales unit name as filename(Column M)

Thanks again
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
 
NirvanamanagerAuthor Commented:
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
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hmm. Please see the attached.
file.xlsm
0
 
NirvanamanagerAuthor Commented:
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
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached to see if this is what you were talking about and trying to do.
file.xlsm
0
 
NirvanamanagerAuthor Commented:
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
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please refer to the attached.
file-v6.xlsm
0
 
NirvanamanagerAuthor Commented:
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
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
 
NirvanamanagerAuthor Commented:
you have been providing brilliant solutions. thank you so much. really appreciate. thanks again
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Kiran! Glad to help.
1
 
NirvanamanagerAuthor Commented:
Thanks a lot. I could which only i could provide you more points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now