1
votes

I have a worksheet with VBA code (on Excel, right-click on the sheet name and View code) that I would like to copy on the same workbook.

When using workbook.copy_worksheet() , the VBA code contained in the worksheet is lost.

I've had a look at the worksheet.vba_code property but it seems to only contain some sheets properties, not the VBA code.

2

2 Answers

1
votes

I think the problem will be that worksheets themselves do not contain any VBA code. This is stored as a blob in the XLSX package and may well contain hard-coded references to particular worksheets. Unfortunately the VBA blobs are not covered by the OOXML specification so there is no way to know. You might be okay if you copy the vba_code property manually but there is no guarantee and it's just as likely that Excel will complain about the file.

0
votes

The workaround I found was to add VBA code to the workbook itself that copies the VBA code from a sheet every other.

I added this in the workbook's VBA code:

Private Sub Workbook_Open()

    Dim CodeCopy As Object
    Dim CodePaste As Object
    Dim numLines As Integer
    Dim sheetNumber As Integer

    Set CodeCopy = ActiveWorkbook.VBProject.VBComponents(Worksheets(1).CodeName).CodeModule

    For sheetNumber = 2 To Worksheets.Count
        Set CodePaste = ActiveWorkbook.VBProject.VBComponents(Worksheets(sheetNumber).CodeName).CodeModule
        numLines = CodeCopy.CountOfLines

        If CodePaste.CountOfLines > 1 Then
            CodePaste.DeleteLines 1, CodePaste.CountOfLines
        End If

        CodePaste.AddFromString CodeCopy.Lines(1, numLines)
    Next
End Sub

Solution based on this and this.