Create 2 reports from raw sales data with VBA

[v_kash]

Need major help with this. I'm not a macro person (more business intelligence). Was wondering if someone can help me with this:

in the file labeled telesales_dailly_summary_form_example, sheets 2 and 5 are WTD Summary Reports. Sheets 36 through 39 and 50 through 54 are each agents resepective daily sales summary. What I need to do, is to create 2 separate files (1 for each agent), that shows Sheet 2, and 36 - 39 as 1 file, and in the other 1, Sheet 5, and then sheets 50 through 54. The WTD Summary will be page 1 in each file, and then the daily summary sheets will follow. The raw data (telesales_dailly_summary_form_example) was generated in SSRS. Can anyone help, or point me in the right direction?


FYI (the Sales Report.xlsx) is how they want it to look. Thanks!!

Thanks

[rvasquez]

Are these worksheets already created and you are just trying to automatically export them into new files? Or are you wanting something that creates new files?

Thanks,
RVASQUEZ

[v_kash]

Rvasquez - thanks for the response..well the raw data file is created by us (they will be run each week for the prior past week..so on Monday, they will run it for the past Mon-Fri), and then export the excel file to their desktop. The macro is needed to take that raw file, and make it look like the output file..hope that makes sense

[rvasquez]

Thank you that makes sense I'll try and get to this as soon as possible if someone doesn't answer it first.

Thanks!

[v_kash]

oh wow. that is mighty kind of you. i know very little about macros, but obviously am appreciative of the fact that you even acknowledged my post! thanks a lot man!

[arlu1201]

Will the sheet numbers remain constant each time you get your raw data? Also, i see that sheet 35 belongs to the same user as Sheet 36. Did you miss it out by mistake?

[v_kash]

Sheet numbers will never remain constant..you're right..i missed that by mistake (typo)..it should be 35 - 39..not 36-39..the unique identifier i imagine, will be combination of the name and the store..

[rvasquez]

Alrighty,

I'm not sure if this is the most efficient way to accomplish what you are trying to achieve but I took a shot.

Attached you will find two workbooks. The workbook entitled "V_Cash_telesales_daily_sales_summary_forum_example(1).xlsm (129.1 KB)" is merely the summary workbook you provided previously. Please open and save this to a folder that suits your needs and then close this workbook. I believe you stated that you receive new data and you'll want to save your new data over this file each time you receive it.

The second workbook entitled "V_kash_SalesReportTemplate.xlsm (82.9 KB)" is the other attachement you provided which contains the preferred layout of the document. This workbook will now be used as a template. Please open this document and save it to the same location you saved the
"V_Cash_telesales_daily_sales_summary_forum_example(1).xlsm (129.1 KB)" workbook.

Next press the F11 key on your keyboard while holding down the Alt key to bring up Visual Basic.

In the project explorer on the Left navigate to Module1.

You will see the below lines of code at the top of Module1

Please Login or Register  to view this content.

Change the below line of code to the Filepath of the "V_Cash_telesales_daily_sales_summary_forum_example(1).xlsm" workbook that you saved previously.

Please Login or Register  to view this content.

Once you have changed this line of code, you can close VBA.

Everything on the Workbook is automated so don't make any additions or deletions as it is a template.

On the first worksheet of the workbook ("Week-to-date"), thre is a command button entitled "Update".

When you click this button a userform will appear with further instructions.

Basically how this workbook functions is it opens and renames all the worksheets in the Summary workbook by combining the Agent Name, Store Name and day of week into a cell and then sets the workbook name equal to that cell. It also gathers the Agent Names and Store Locations from the Summary workbook and adds them to the comboboxes on the userform.

Next, it enters a formula into C1 & D1 of all the worksheets in the Template workbook, which again combines the Agent Name, Store Name and day of week, copies and pastes the values into the cell to be used for reference.

Then, it looks up the values in the cell C1 of each of the worksheets on the Template workbook (which are now equal to the names of the worksheets in the Summary workbook) and uses the find method to locate each of the fields and return the value.

Finally, it closes the Summary workbook and saves the template workbook as a new file using the Last Name, Location and the titles Sales Report as the file name. The new file saves in the same location you saved the Summary and Template workbooks. The macro closes the new file and reopens Template workbook to perform the next one.

Please let me know if you have any questions and if this works for you! Thanks!