Help needed in copying a template into another worksheet

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Help needed in copying a template into another worksheet

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Re: Help needed in copying a template into another worksheet

Post by 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

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Help needed in copying a template into another worksheet

Post by 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
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Re: Help needed in copying a template into another worksheet

Post by chamdan »

:thankyou: Rudi, i will give this try.

Have yourself a great weekend.

Chuck