Help needed in copying a template into another worksheet

[chamdan]

Hi,

I am having a problem when copying the template and creating a new worksheet that the copied template will be pasted onto it and keep the same format. Each time I run the macro the created worksheet becomes a picture and not a worksheet.
I am certainly doing something wrong. I did use the record macro to build this macro. On the other when tjhe paste is done the workbook size increases to almost 10 Mb.

Would you mind have a look at it. I use here Excel 2010.

Chuck

[chamdan]

Rudi,

I finally managed to make it work by removing the following from my macro:

Code: Select all

    ActiveSheet.CheckBoxes.Add(312, 324, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 324, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 234, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 234, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 234, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 324, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 219, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 219, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 219, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 249, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 249, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 249, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 264, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 264, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 264, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 279, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 279, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 279, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 294, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 294, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 294, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 309, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 309, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 309, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 444, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 444, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 444, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 459, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 459, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 459, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 474, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 489, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 504, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 517.5, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 517.5, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 517.5, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 354, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 354, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 354, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(312, 369, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(399.75, 369, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 369, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 384, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 399, 17.25, 14.25).Select
    ActiveSheet.CheckBoxes.Add(487.5, 414, 17.25, 14.25).Select

[Rudi]

Hi Chuck,

There is no need to copy the cells, data and objects on the sheet if you want an exact replica of it.
You can simply duplicate the sheet in its entirety.

This code will do the job as required...

Code: Select all

Sub CopyTemplate2()
    Sheets("Template").Copy After:=Sheets("Template")
    On Error GoTo EH
    ActiveSheet.Name = "BIN001"
    Exit Sub
EH:
    MsgBox Err.Description, vbExclamation
End Sub

[chamdan]

Rudi, i will give this try.

Have yourself a great weekend.

Chuck