0

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.

3
  • 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.
    – Knuto01
    Jul 31, 2014 at 9:23
  • Sorry to ask, can you confirm that this code doesn't exist in the ThisWorkbook (under Visual Basic)
    – Dave
    Jul 31, 2014 at 9:25
  • It is not under thisworkbook it is under "sheet10 (input-metrics)"
    – Knuto01
    Jul 31, 2014 at 9:36

3 Answers 3

1

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)

8
  • I get a subscript out of range on the second line "Workbooks("testq.xls").Worksheets.Add"
    – Knuto01
    Jul 31, 2014 at 9:58
  • I have now fixed the VBA code issue by copying the cells instead of the sheet. But the colour issue remains.
    – Knuto01
    Aug 1, 2014 at 7:32
  • I'm not sure what makes the colour scheme... Is this colour done by a theme or similar, or by the VBa?
    – Dave
    Aug 1, 2014 at 7:33
  • 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.
    – Knuto01
    Aug 1, 2014 at 7:36
  • I don't know, sorry
    – Dave
    Aug 1, 2014 at 7:51
0
Sub LuuVaXoaCode()
Dim Theo_Doi
Theo_Doi = ActiveWorkbook.Name
Theo_Doi = ActiveSheet.Name
Sheets(Theo_Doi).Copy
ActiveSheet.DrawingObjects.Delete

'Delete code cua worksheet
With ActiveWorkbook
        For Each Theo_Doi In .Worksheets
            With .VBProject.VBComponents(Theo_Doi.CodeName).CodeModule
                .DeleteLines 1, .CountOfLines
            End With
        Next
 'Hien cua so Save As
Application.Dialogs(xlDialogSaveAs).Show
ActiveWindow.Close
End With
End Sub
1
  • 4
    While this may answer the question, it would be a better answer if you could provide some explanation why it does so.
    – DavidPostill
    Feb 1, 2015 at 7:20
-2

I found something about how to copy office theme colors from one workbook to another using VBA and about Excel colors somehow getting changed?

...maybe this can be useful too, it includes VBA code: VBA code for working with Excel 2007 themes

Hope it helps :) Have a nice day.

1
  • 1
    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.
    – Hastur
    Sep 16, 2014 at 11:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .