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
Code:
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
Code:
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
