Copy macro's, is it possible?

[Airborne]

Hello, is it possible to copy macro's from one workbook to another workbook? Goal is that the button in the new workbook can use the copied macro. I have a workbook (xlt) with a macro. I copy and save a sheet out of the workbook as a xls. In the copied sheet a button (assigned to a macro remains). The problem is that the button is there but the macro has not been copied. Is it possible to copy the macro with the sheet?


Thanks.

 

[Ekim]

See “Copying Modules Between Projects”
http://www.cpearson.com/excel/vbe.htm

Note Mr. Pearson’s comment :
"In all versions of Excel, the VBProject must not be protected. If it is, these procedures will fail. In Excel 2002, you must have "Trust Access To Visual Basic Project" enabled. To enable this setting, go to the Tools menu in Excel, choose Macros, Security, then the "Trusted Sources" tab, and put a check next to "Trust Access To Visual Basic Project". Otherwise, you will get errors".

HTH

Mike

 

[Barry Katcher]

You can copy a macro the same way you copy anything else in Excel. Just have both workbooks open, open the Visual Basics Editor, navigate to the module with the macro, and highlight it. Then, click Copy on the VISUAL BASIC EDITOR (not the spreadsheet) toolbar, or right-click and click on copy. Then navigate to the module in the workbook you want to copy it to and click on Paste.

 

[Airborne]

Thanks guys. Barry your idea is usefull but I want to put those actions in a macro.
Mike thanks for the link. I'm going to give it a try. I just want to use the macro+button in the saved sheet (the sheet from the workbook where the macro+button came from).

 

[Airborne]

Mike, I've tried your link and used the code

Sub CopyOneModule()

Dim FName As String
With Workbooks("Book2")
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName

End Sub

It worked but only if I shutdown my virusscanner. The virusscanner went totally insane. So it does the trick, copy macro´s from my workbook to the moved sheet from the workbook, so I can use the macro´s I need in the new sheet but I can´t use it.


The sky is the limit

 

[Ekim]

Mr. Pearson’s macro is simply copying a macro module, and its contents, from a workbook to a temporary text file, then copying the contents from that text file to another workbook. Why this would upset your anti-virus program is a mystery. Are you copying data across directories on a server? If so, your network security may be the problem.

The following model works perfectly on my machine. You may wish to test it on your set-up.

Create a template file named “myTemplate.xlt” (on my machine, the file is saved to some directory on my C drive).

“myTemplate.xlt” contains the following:

3 worksheets, named:
Sheet1
Sheet2
Data

2 macro modules (Module1 and Module2)

The objectives:
1. Copy the “Data” sheet from “MyTemplate.xlt” to a new file, and name the new file as “Data.xls” (a normal workbook). Data.xls is then saved to C:\Temp3.
2. Copy Module2, including its contents, from “myTemplate.xlt” to “Data.xls”.

Module1 contains a macro named “SaveSheet” that performs item 1 above and calls another macro that performs item 2 above.

Module2 contains a simple macro named “myFormat” that adds some formatting to worksheet “Data” (for testing purposes, any macro will do).

Sheet1 in “myTemplate.xlt” contains a button assigned to the “SaveSheet” macro. Worksheet “Data” in “myTemplate.xlt” contains a button assigned to the “myFormat” macro.

Module1

Sub SaveSheet()
Dim sPath As String
Dim FName

sPath = "C:\Temp3\"

Application.ScreenUpdating = False

FName = "Data"  ' New file name
Application.DisplayAlerts = False
Worksheets("Data").Copy
ActiveWorkbook.SaveAs sPath & FName

' Copy Module2 to the new file Data.xls
CopyOneModule2

ActiveWorkbook.Close SaveChanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


Sub CopyOneModule2()
'http://www.cpearson.com/excel/vbe.htm
Dim FName As String

With ThisWorkbook
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Module2").Export FName
End With

Workbooks("Data.xls").VBProject.VBComponents.Import FName
Kill FName
End Sub

Module2

Sub myFormat()
Dim rng As Range

Set rng = Worksheets("Data").Range("A1:H15")

With rng
  With .Font
    .Name = "Arial"
    .Size = 12
  End With
End With
End Sub

Notes:
The line “Kill FName” has been added just before the End Sub in Mr. Pearson’s macro. This is not in Mr. Pearson’s original macro but it seems to be a good idea to remove the temporary text file.

HTH

Mike

 

[Airborne]

Great Mike! Thanks for your trouble. I'm going to try it out and get back to you.
I use Norton Antivirus and when I tried to save the workbook containing the code I showed you before, I got a message that the book could not be saved because it contained a virus. When I removed the code the message didn't show up. I then saved the workbook with the code after I disabled Norton. When I then activated Norton again it went crazy, warnings popping up all the time. I had to delete the file to get Norton to shut up. I used it on a stand alone pc.

Anyway thanks,

 

[Airborne]

Hello Mike, I've tried your idea. And as soon as I want to save the workbook as myTemplate.xlt, Norton shows up telling me that it won't save the workbook because it has found a virus called "Bloodhound.ExcelMacro". Since we also use Norton at the office, I don't think it's going to work.




Thanks anyway.

I've been playing around with the macro's. I disabled Norton and tried the macro's. It all works as I would like it to work. Norton seems to have a problem with this line

.VBProject.VBComponents("Module2").Export FName

 

[Ekim]

And as soon as I want to save the workbook as myTemplate.xlt, Norton shows up telling me that it won't save the workbook because it has found a virus called "Bloodhound.ExcelMacro

This "Bloodhound.ExcelMacro” seems to be a Norton thing. Some comments and a possible work around from Tim Zych:http://makeashorterlink.com/?B2B256348

A detailed search on the Net may turn up alternative solutions. It may be worth a call to Norton.

It’s all connected with Chip Pearson’s macro. Like I stated previously, the model works perfectly for me (I use AVG Anti-virus software).

Anyway, the only other thought that I have on this matter is to open the template file and then programmatically save it under a different name. The macro will also delete all worksheets in the new file except for the one you want. By default, the new file will have all the macros. The Pearson macro will not be needed.

I will try to have something for you this time tomorrow.

Mike

 

[Airborne]

Thanks Mike. I checked the link you showed me and I guess it's worth a try to contact Norton.