Macro to copy two sheets to a new workbook and include a vba module?

[tonywatsonhelp]

Hi Everyone,

I would like a macro that when run creates a new document with copies of Sheets "New Book1" & "New Book 2" in it but also copy over the macros,
Now if it easiest then copy all macros, if not then copy Module "New_Macros"
or if its easier to name the macros then copy
Codes1
Dropdown
Helper

any ideas would be great

thanks

Tony

 

[Mescoman]

Ive not heard of a way to copy macros, so my first idea would be to make a macro which does this:

- Save your current workbook
- Save your current workbook again under a different name
- Delete all the sheets you dont want to use
(- Open the original workbook again )

Theres probably a more efficient way to do this though, I know you can create and edit new excel documents, I just dont know how (if its even possible) to copy macros/modules from one workbook to another.

 

[Mescoman]

Here I made a code which should work and do what I described. It doesnt open the original workbook again. I commented out a bit which saves the new document as you may not want this (and because Im not too sure how the SaveAs function works and how to get it to save in a specific location), but this should get you started.

Sub mrexcel_generate_excel_workbook()

'We add a warning asking user to save data first
If MsgBox("Would you like to save your document first? Sheets will be deleted", vbYesNo + vbQuestion, "My Title") = vbYes Then
        ActiveWorkbook.Save
End If

'Disables alerts (handy if you are deleting lots of sheets)
Application.DisplayAlerts = False

'Now we delete the other worksheets
For Each sht In ThisWorkbook.Worksheets
    If sht.Name <> "New Book1" Then
        If sht.Name <> "New Book2" Then
        Sheets(sht.Name).Delete
        End If
    End If
Next sht

'Reenables alerts (very important for regular excel use)
Application.DisplayAlerts = True

'ActiveWorkbook.SaveAs ("FileNameHere")


End Sub

 

[Fluff]

Have a look here Copy modules from one workbook to another using VBA in Microsoft Excel

 

[tonywatsonhelp]

Thank you mescoman,
Very helpfull.
Thanks
Tony

 

[RoryA]

If you can, it would be simpler to have a template file with just those two sheets and the module in it.

 

[aashish83]

Have a look here Copy modules from one workbook to another using VBA in Microsoft Excel

Hi Fluff where do i put this code as i cant seem to run it?

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub

Any chance i can combine this with the following code so that it creates the new workbook as below and also copies the modules associated with the original workbook or if i can define which modules to copy?

Sub FSRA()

Dim rng As Range
With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
Set rng = ActiveWorkbook.Worksheets("BI").Range("A1:B28")
.Copy
ActiveWorkbook.Worksheets("BI").Range("B29:B31").EntireRow.Hidden = True
ActiveWorkbook.Worksheets("BI").Range("A1:B28") = rng.Value2
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52
End With

End Sub

 

[Fluff]

Please start a new thread for this question. Thanks