CODEWITHSUNDEEP
excelvbacopy
user12408889
user12408889

Reputation:

How to duplicate a worksheet without duplicating its Worksheet Private sub code

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

Upvotes: 0

Views: 257

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 2

Related Questions