VBA to save reports, generated using macros, to specific folders

  • I have a set of macros to generate multiple reports which works fine. However, I would like to include a VBA script to save the generated reports into specific folders depending on the name of the report that is being generated. When I generate reports using macros, the files are saved as per the name of the city to the local drive. What I'm looking for is to have the macro search up the corresponding folder (matching the name of the report) in the local drive and place it under a folder with the current date. For example, my macro saves all the reports (city names) for my client AFD to D: after which I have to manually place the files to its respective folders. I would like for it to search the directory for a folder called AFD > city name > current month > current date > save. There are a total of 25 clients and 175 cities.


    Here's the VBA code I use to generate the reports. Any help is greatly appreciated.


  • Re: VBA to save reports, generated using macros, to specific folders


    Can't really help without knowing where in the sheet I would pull the client/city name from. however, this should help: https://msdn.microsoft.com/en-…kbook-saveas-method-excel


    edit: just as an observation, if you want to speed up your code a little I would consider using advanced filter to speed up the "copy/paste" process, and you could set row height without the loop for the entire range. An additional speed boost could be had from turning off screen updating (assuming you aren't doing that from a parent sub).

  • Re: VBA to save reports, generated using macros, to specific folders


    Can't test this at the moment as I'm not currently near a machine with excel. But something along these lines should sort you out. hopefully it's easy enough to follow so you can tweak where required.


  • Re: VBA to save reports, generated using macros, to specific folders


    Trunten - Your resolution works as expected and saved me a lot of trouble. Thank you very much.

  • Re: VBA to save reports, generated using macros, to specific folders


    Hi Trunten - I have had to include a Year folder into my structure for easier management. I tried editing the code myself but only managed to generate an error. Could you please help me with this?

  • Re: VBA to save reports, generated using macros, to specific folders


    Thank you. Well, its the same code that you helped with earlier wherein it would save the generated reports into existing folders by looking up the client name in column B, city name in column I and then search for current year, then current month, and finally current date where the report should be saved. Presently the macro is configured to save the file in the following structure: client name> city name > current month > current date > save. I would like to modify it to save it by client name > city name > current year > current month > current date > save. I am reattaching the report template for reference. Your help is much appreciated.


  • Re: VBA to save reports, generated using macros, to specific folders


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!