Add VBA code to dynamically created Excel sheets

Question

I am working on an Excel workbook that has one initial sheet. This first sheet is a form that asks the users how many items in their project they have. The macro then generates a sheet for each item in their project.

Each generated sheet has buttons that perform specific functions, and these buttons have click events tied to them in the sheets code.

I have coded out all the functionality I need in the sheet in a static sheet.

I was wondering if there was a way to take this code and import it into all the dynamically created sheets once all the formatting and buttons are created by the form.

Any help would be greatly appreciated!

just create a copy of the sheet, all the code will be copied over. — cyboashu
– cyboashu, Commented Feb 1, 2018 at 22:02
Hi @user2796352 - Can you please share what you have so far? — dwirony
– dwirony, Commented Feb 1, 2018 at 22:06
I wouldn't even know what to share, its around 800 lines so far — user2796352
– user2796352, Commented Feb 1, 2018 at 22:09

DecimalTurn · Accepted Answer · 2018-02-02 06:52:20Z

2

You could create a sheet named "Template" (potentially hidden) that would contain all the code and buttons and then use something like this:

Sheets("Template").Copy After:=Sheets(Sheets.count)

At this point, the Activesheet will be the newly copied sheet, so you can define a variable to refer to it and make all the changes you need. For example:

Dim ws as Worksheet
set ws = Activesheet
ws.Range("A1") = "test"

answered Feb 2, 2018 at 6:52

DecimalTurn

4,4033 gold badges20 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2796352 Over a year ago

Where do I set the ws variable? Is this in a separate Module?

shmicah · Accepted Answer · 2018-02-01 22:55:58Z

As @cyboashu said, just copy the sheet and it will copy all formulas and formatting over. At that point, any additional changes that are needed can still be made to the individual sheets. They are not linked copies.

Here's a quick and dirty macro to copy worksheet(1) to the end of the workbook and rename. If you need to do any formatting, you can do it immediately after the paste using ActiveSheet. Assigning your new Activesheet to a variable to reference later is better practice but I'm not 100% sure how to do that without digging in further. Maybe someone can elaborate.

    Sub copysheet()
    Dim Mainws As Worksheet
    Dim Mainwb As Workbook
    Set Mainwb = ThisWorkbook
    Set Mainws = ThisWorkbook.Worksheets(1)

    i = Mainwb.Worksheets.Count

    With Mainwb
       Mainws.Copy after:=Worksheets(i)
       Worksheets(i + 1).Name = "Copied Worksheet"
    End With
    With ActiveSheet
        Cells(1, 1).Value = "Copied Sheet"
        'other formatting changes
    End With

    'increment i variable if you plan to make more copies in a loop
    'i = Mainwb.Worksheets.Count

    End Sub

Collectives™ on Stack Overflow

Add VBA code to dynamically created Excel sheets

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related