Macro Button to Insert a Cells as a Template

[BettyBoop0916]

Hi there,

I need help in producing a macro button to insert cells from a sheet entitled data to a new sheet. Now, this button has to work on any new sheet I create.

For example, I have my workbook set up as such:

Sheet 1: Data
Sheet 2: Template
Sheet 3: New
Sheet 4: New
Sheet 5: New

And so on...

Now, when I copy the template sheet (which has the macro button on it), I want the macro button to still be able to be used to insert those cells.

But I stumble, because the only way I was able to set this up was as such:

Private Sub CommandButton2_Click()
Sheets("Data").Select
Sheets("Data").Range("C1:I5").Select
Selection.Copy
Sheets("Template").Select
Selection.Insert Shift:=xlDown
End Sub

But as you see, I've had to select only Template when I select the sheets. I need to be able to make it so that where I've placed "Template", I can place something to the effect of any new sheet created.

Does this make sense?

Can someone help on this subject?

 

[bertie]

Try this:

The first thing the code does is to copy and rename the Template sheet.

    [color=green]'copy and rename the template[/color]
    sSheetName = "New_" & ThisWorkbook.Worksheets.Count
    Sheets("Template").Copy After:=ThisWorkbook.Worksheets(Worksheets.Count)
    ActiveSheet.Name = sSheetName

Then it copies and pastes:

    [color=green]'copy and paste[/color]
    Sheets("Data").Range("C1:I5").Copy _
        Destination:=Sheets(sSheetName).Range("[COLOR="Red"]C1[/COLOR]")

And that's pretty much it.

The command button would be on the Template sheet.
The code should be in the Template sheet module.
Here is the full code for the command button:
Edit the destination range to suit your needs.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton2_Click()
    [color=darkblue]Dim[/color] sSheetName [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=green]'copy and rename the template[/color]
    sSheetName = "New_" & ThisWorkbook.Worksheets.Count
    Sheets("Template").Copy After:=ThisWorkbook.Worksheets(Worksheets.Count)
    ActiveSheet.Name = sSheetName
    
    [color=green]'copy and paste[/color]
    Sheets("Data").Range("C1:I5").Copy _
        Destination:=Sheets(sSheetName).Range("[COLOR="Red"]C1[/COLOR]")
[color=darkblue]End[/color] [color=darkblue]Sub[/color]