How do I automate macros to be placed in many workbooks that won't be on my PC

[K0st4din]

Hello everyone,
I have read many things on the internet, but I could not find a solution to my problem. I don't know if there will ever be a macro that can solve my problem. In one workbook I have 28 worksheets. then with a macro I separate 14 workbooks with 2 worksheets in each workbook. I have 2 macros in vba, the 1st is in Thisworkbook, the 2nd is in Module. This is where my problem starts: I transfer these two macros in all 14 workbooks with copy/paste, which totally kills me. Because these macros make it so that they put restrictions on copying, printing, etc., and the 2nd one after certain days totally deletes the workbook. So, if it's just for my excel, I read how to do things, like what file to always have the macros, but in this case, as you can see, it's about 14 workbooks that must have these restrictions, so that different users cannot do anything with the files. I read that there was a way to make a macro or something that was sent to every single user and when they installed it, the macros were implemented in their excels. But that's not a solution because firstly they won't be able to handle it and secondly there's no way I can be sure they will. So my dilemma remains - how can I as quickly as possible put these macros into each workbook and then be able to send the files to the specific people. I hope I have explained well what I am trying to automate. Of course I remain available if I need to explain anything further. And I'm really hoping for some help because I'm desperate to copy and paste every month. Thanks in advance!

 

[Edgar_]

Google how to export code modules. It involves using the workbook's VBProject object, like:

Sub ExportModule(moduleName As String, destination As String)
    ThisWorkbook.VBProject.VBComponents(moduleName).Export destination
End Sub

Sub ImportModule(moduleFilePath As String)
    ThisWorkbook.VBProject.VBComponents.Import moduleFilePath
End Sub

Make sure to trust access to the code project in the trust center.

 

[K0st4din]

Hi Edgar_,
I'll give it a try, for sure.
Because just because of this google that was doing a perfect job for me, but for some time, the tables that I send lag so much and slow down that one could drink a bottle of wine.
The first time this problem occurred, I thought something was wrong with the table. I started all over again to make the tables, no formatting, formulas, it's like I write the numbers by hand. In a word, the most ordinary table (yes, of course there are a lot of numbers, but they are far less than the permissible, as a limit), but despite everything the logging and inscriptions like: Please wait......., straight it got awful.
And so I forced myself to change everything with macros and send the tables via emails, but then the protection of not being able to print, copy, download the file, etc. fails. Now I will try what you suggest and test it. Then I will write about the final result. Just to ask, these two macros, I guess should be in the parent file (from where I extract the 2 worksheets, right?

 

[K0st4din]

Hello again, apparently I don't do something like other people.
I placed these two macros in the main workbook, from where I then split into 2 worksheets.
However, the 1st macro written like this I can't insert it into a button to activate it, and also moduleName - when I wrote for example Module2, it showed me an error, then on moduleFilePath - do I have to show the path in my computer or will it do it automatically where the file itself is located.
So, I guess I didn't understand it. And I will something in the VBA itself in Thisworkbook I also have macros - how to move it to the already new workbook with the two worksheets.

Thank you in advance.

 

[Edgar_]

these two macros, I guess should be in the parent file (from where I extract the 2 worksheets, right?

Yes, they go in the parent file. They're working examples to export/import a code module. You just have to adapt them. Another strategy is to simply create 14 copies of the file and programmatically remove the sheets that don't belong. That way you will not have to do any exporting and importing.

 

[K0st4din]

Well I think the second suggestion can't work for me because with a macro, I select and take certain worksheets, then the finished file with both worksheets has an auto-generated name as well.
The question is that I am not very aware of macros and would you be able to help me. As I read, this was just an example, and I don't think I will be able to finalize (much to my regret) ((

 

[Edgar_]

Hi

the 1st macro written like this I can't insert it into a button to activate it

You have to call it from another sub, like:
ExportModule "Module2", ThisWorkbook.Path & "\Module2.bas"

do I have to show the path in my computer or will it do it automatically where the file itself is located

This snippet will use the current location:
ThisWorkbook.Path & "\MyDesiredFileName.bas"

I don't think I will be able to finalize

There are many ways to approach this. You could upload a sample file to see what I can come up with.

 

[K0st4din]

Ahh, you mean, (I didn't get it the first time I read it), yes, to make 14 or as many copies as I need and only have 2 worksheets left inside. However, then how do I give them the necessary names to know 1st which city it is for, 2nd for which etc.

 

[Edgar_]

then how do I give them the necessary names to know 1st which city it is for, 2nd for which etc.

I don't know what code you're using for splitting a file with 28 worksheets into 14 files with 2 worksheets each. It all comes down to seeing what you have to figure out a working approach.

 

[K0st4din]

Since I can't upload the file, I'm attaching a photo.
In column E3 the orange, I choose which city from a drop down menu.
Then with Vlookup in column A3, A4 the city and city+total appear. After that I press Show Sheets (the blue button), this form appears and inside I already have the requested cities marked (now just to mention that in column A3 and down, with this macro, I can also put other Vlookups to call not only 2 worksheets and again with the UserForm to be exported to a separate workbook. So it can be seen that the format is excel and is xlsm , I press OK and the finished file is already with the extracted information in the same place where the parent file is.