Exporting Files

Hello Everyone,

So I created a VBA that creates a report and saves the file based on what is in cell H5 on the Input page and saves it to the directory that is in cell H15 on the Input page. In cell H5 I can select a company name and there is over 30 company names. Rather than running the macro 30 times, I was wondering if there was a way it can go through all the entire list and save them. Here is my current code that will run it for 1 company.


The very top part of the code is just some formatting on my sheets which would have to remain the same way for all 20 times it runs it, so maybe loop it somehow? By the way the company names that are in cell H5 are using a Data Validation and the full list can be found in the Sources worksheet H2 to H32. Also what if I were to add a company name? Can we set the paramets for H2 to H100? and then just skip over it if the cell is blank.

Thank you!!

Please Login or Register  to view this content.

Hello,

I appologize I did not include this information. The company names can be found on the worksheet Sources in H2:H22, but potentially this list could grow so lets set it from H2:H100 and then have it skip the blanks?

I have attached a sample workbook with the same cell references.

Export Macro.xlsx

Thanks!!

Originally Posted by alansidman

I have written some code and would like to test it before I give it to you, but you have no sample data in your workbook. If you provide a workbook with sample data, then I can be sure that the code will work. From a confidentiality standpoint, make sure that the data is fictitious.

Is it okay if I send you a sample workbook as a PM?

There are plenty of examples in this forum about selecting and copying.
I am pretty sure that every single thread will mention that you should not use Select (with the very odd exception)

Search for that as well as "copy variable length range" or something similar.
It will open your eyes.
I am not negative here, just repeating to you what I was told when I was at the same spot you are.

Instead of this

Please Login or Register  to view this content.

try this

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Here you select 1,048,576 x 16,384 (in 2007) = 17,179,869,184 cells
Is that really what you need to copy?
Have a look at something like this

Please Login or Register  to view this content.

(this is a range fom A1 to the last used cell in column A)
or

Please Login or Register  to view this content.

(this is a range fom A1 to the last used cell in column A and extends it to column F, the 6 is the 6th column)

instead of

Please Login or Register  to view this content.

you could use

Please Login or Register  to view this content.

etc etc


If you go through your whole code you'll find more that should be changed.
Search this forum or google every instance of your code and you'll see (the light)

Good luck

Okay here you go I put a better sample together. Basically the reports are all the blue sheets BS_Entity, IS_Entity,

The new data is updated when the cell G10 is updated on BE_Entity and IS_Entity. When you go to the input page and select to run the report for Company A it should automatically update G10 on BS_Entity and IS_Entity, which the individual report already does!

So if you are on the input page in cell H5 select Company A and run the macro, you will see it export based on what is stored in cell H15.
Now I need to run the second macro "Run All Entities Report" which dumps about 20 files into that directory. Esentially its the same macro it just runs it as many times as needed. The company names are stored in Sources H2:H22, but this list could potentially grow so I would set the paramaeters to H2:H100 just incase.

Keep in mind this macro breaks all the links on the BS_Entity and IS_Entity worksheet.
In addition it would be cool if we could add checkboxes which allowed me to select which of the reports I want to include in the export. For example BS_Entity [ ], if not checked then it will not include it in the export, and it BS_Entity[X] was checked then it would include it in the export.

Export Macro2.xlsm

Also jolivanes, I agree the reason I have .Select is because I had the macro record all my key strokes. Going forward I think it would be a good idea to go back and modify my entire code to eliminate unnecessary redundant coding that was recorded by the macro. In my first course I learned that there is more than one way to write a macro, but the expert is the one that knows how to write it in the most simplified manner.. So hopefully I can start looking at other peoples examples and begin to learn! Thank you for all the tips I will go back and try to eliminate all instances of .Select