CODEWITHSUNDEEP
vbaexcel
Hafiz Haziq
Hafiz Haziq

Reputation: 9

How do i insert a macro when a new sheet is created

Is it possible to insert a macro when a new sheet is created. For example, i have a main worksheet which has all the data, and when i click on a command button, it filters out the rows and columns i need and paste it on a new worksheet called Europe. Is it possible to have a command button ready set there when the "Europe" worksheet is created?

Upvotes: 0

Views: 1921

Answers (1)

Automate This
Automate This

Reputation: 31364

The following macro will create a new sheet, set it's name, create a new button on the sheet, change the label and assign an existing macro to the button.

Sub AddWorksheetWithMacroButton()
    Dim btn As Button
    Dim wks As Worksheet

    'Create new worksheet
    Set wks = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    'Set worksheet Name
    wks.Name = "MyNewSheet"

    'Add new button to worksheet (left pos, top pos, width, height)
    Set btn = wks.Buttons.Add(390, 61.5, 94.5, 31.5)

    'Lable button
    btn.Characters.Text = "Button Label"

    'Assign existing macro (Macro1) to button
    btn.OnAction = "Macro1"
End Sub

Upvotes: 1

Related Questions