0

I'm trying to duplicate a worksheet with a macro, but when doing so, to private sub is duplicated as well, what I don't want to happen because it interferes afterward with another module macro. I have seen this post Copy a worksheet without copying the code and tried, but it doesn't work properly. Some ideas on how to do that?

Sub Export()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Application.CutCopyMode = False

Sheets("CB").Select
MonthID = Range("N2").Value
YearID = Range("O2").Value
saldoID = Range("O18").Value

ActiveSheet.Unprotect
ActiveSheet.Copy After:=Sheets("CB")

' Get the code/object name of the new sheet...
Dim Code As String
Code = ActiveSheet.CodeName

' Remove all lines from its code module...
With ThisWorkbook.VBProject.VBComponents(Code).CodeModule
    .DeleteLines 1, .CountOfLines
End With

The last step (.DeleteLines 1, .CountOfLines) always causes an error: "Can't enter break mode at this moment" - Run time error 1004 Application defined or object defined error.

Why, what is wrong or missing?? Thanks

  • The [`CodeName`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.codename) of the new sheet is not the same as the code in the sheet code module... – BigBen Jan 25 '20 at 14:07
  • what can I do instead? I don't really get it –  Jan 25 '20 at 14:09
  • `Code = ActiveSheet.CodeName`. – BigBen Jan 25 '20 at 14:10
  • thanksT that works but at the .deleteLines 1, .countOfLines I have always a 1004 error message –  Jan 25 '20 at 14:15

1 Answers1

2

One simple way:

  1. copy sheet to new workbook
  2. save new workbook as .xlsx
  3. close new workbook
  4. re-open new .xlsx workbook
  5. copy the macro-free worksheet back to original workbook

The VBA code for this is simple; you could also include:

  1. re-close the.xlsx
  2. kill (delete) the .xlsx workbook
Gary's Student
  • 95,722
  • 10
  • 59
  • 99