move worksheet to another workbook, without the vba code but keeping the colour scheme

Question

I current have some code that first copy a worksheet do some cleaning and then move it to another workbook.

My problem is that when i copy or move a sheet it also copies the vba code that is attached to the sheet object.

Is there a way to avoid this?

Also when i move the sheet to another workbook the colours changes. Is there a move option that allows to keep the source formatting, much like the paste in the excel ribbon.

Currently I'm exporting the colour scheme and then importing it to the new workbook, but that requires unlocking the source workbook and with the new (2013) security it takes a very long time.

Ideas are highly appreciated.

Solution

My guess is, your code has something like

ActiveWorkbook.Sheets(1).Copy

This will copy the VBa.

If you want to copy it without the VBa, you'll have to do it by selecting all the rows and cells.

ActiveWorkbook.Sheets(1).Cells.Copy
Workbooks("abc.xls").Worksheets.Add
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteAll

Source for above

Or, you can continue as you are, but just delete any VBa from the new worksheet

This example deletes all VBa from a project

    Sub DeleteAllVBACode()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    Set VBProj = ActiveWorkbook.VBProject

    For Each VBComp In VBProj.VBComponents
        If VBComp.Type = vbext_ct_Document Then
            Set CodeMod = VBComp.CodeModule
            With CodeMod
                .DeleteLines 1, .CountOfLines
            End With
        Else
            VBProj.VBComponents.Remove VBComp
        End If
    Next VBComp
End Sub

Source (And other examples)

Comments

[Knuto01]

Yes, i have some vba code that is executed on opening the sheet. That code is only in sheet 1, but is copied to sheet 2 when i copy sheet 1.

[Dave]

Sorry to ask, can you confirm that this code doesn't exist in the ThisWorkbook (under Visual Basic)

[Knuto01]

It is not under thisworkbook it is under "sheet10 (input-metrics)"

[Dave]

I'm not sure what makes the colour scheme... Is this colour done by a theme or similar, or by the VBa?

[Knuto01]

When I add a new workbook, that workbook is using whatever default colour scheme that pc has set up. When i then move the sheet to that workbook it will use the new colour scheme, so that if a cell is using f.eks. the 12th colour in the scheme it may be a different colour. Is there a way to copy the colour scheme or to keep the sheets colour scheme when moving. and without having to unlock all sheet in the source workbook.

[Dave]

I don't know, sorry

[Hastur]

Welcome here. The links after some time can disappear. So it's better to spend some words to describe what you find in those reference. This to avoid that your answer may become useless.

[DavidPostill]

While this may answer the question, it would be a better answer if you could provide some explanation why it does so.

[luckyguy73]

anyone wanting to use the second example deleting all vba they need to follow this instruction. 2 steps, step 1: "In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error."

[luckyguy73]

Step 2: "Next, you need to enable programmatic access to the VBA Project. In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model."

[Dave]

@AshtonMorgan, thanks, but feel free to edit my post to include this! It looks to be a very important piece of information. Thank you again.