CODEWITHSUNDEEP
vbaexcel
ExamFML
ExamFML

Reputation: 21

Copy code to newly created workbooks' ThisWorkbook module (VBA)

I have a macro that creates 5 new workbooks and populates them with some info. I want to make sure that the end user saves the created workbooks with a default filename. To do that, I wrote a workbook event code that runs upon saving the file and populates the file name field automatically.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Set Wb = ThisWorkbook
    Application.EnableEvents = False
    Co = ThisWorkbook.Sheets(1).Name
    If InStr(1, Wb.Name, Co) = 0 Then
        Filename = Co & " " & Format(Now, "yyyy-mm-dd")
        With Application.Dialogs(xlDialogSaveAs)
            Call .Show(Filename, xlOpenXMLWorkbookMacroEnabled)
        End With
    Else
        Wb.Save
    End If
    Application.EnableEvents = True
    Cancel = True
End Sub

This works exactly like I want it to but I want to be able to put this code in all 5 workbooks' ThisWorkbook module as they are created using VBA.

Is there a way to accomplish this task?

Upvotes: 1

Views: 591

Answers (2)

YasserKhalil
YasserKhalil

Reputation: 9538

Try this code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb          As Workbook
Dim ws          As Worksheet
Dim co          As String
Dim fn          As String

Set wb = ThisWorkbook

Application.EnableEvents = False
    For Each ws In ThisWorkbook.Worksheets
        co = ws.Name
        If InStr(1, wb.Name, co) = 0 Then
            fn = co & " " & Format(Now, "yyyy-mm-dd")
            With Application.Dialogs(xlDialogSaveAs)
                Call .Show(fn, xlOpenXMLWorkbookMacroEnabled)
            End With
        Else
            wb.Save
        End If
    Next ws
Application.EnableEvents = True
Cancel = True
End Sub

Upvotes: 0

John F
John F

Reputation: 276

Yes. In VBA you need to add a reference to Microsoft Visual Basic For Applications Extensibility 5.3 (or whatever version you have). In your Object Browser you will notice a new library called VBIDE.

You will need to change the Developer Marco Settings in Trust Center...Macro Settings so that "Trust access to the VBA object model." is ticked.

A good summary with example is here.

Upvotes: 1

Related Questions