Copying a worksheet that holds worksheet code in a VBA project to another workbook

[sspatriots]

I found the following statement on a Microsoft learning website:

"If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied Worksheet object. The newly created workbook holds the Application.ActiveWorkbook property and contains a single worksheet. The single worksheet retains the Name and CodeName properties of the source worksheet. If the copied worksheet held a worksheet code sheet in a VBA project, that is also carried into the new workbook."

I have a macro that goes out and gets a worksheet from another workbook and copies it into my workbook. However, the worksheet code does not come with it. Am I doing something wrong or misinterpreting the statement?


Thanks, SS

Sub CopyPRODSCHEDFromClosedWB()
Application.ScreenUpdating = False

    Set closedBook = Workbooks.Open(Filename:="H:\DEM Shop Files\MRL Production Schedule\DEM Production Schedule.xlsm")  'Selected File full path
    closedBook.Sheets("COMM - In Production").Copy Before:=ThisWorkbook.Worksheets("2023")
    closedBook.Sheets("COMM - Completed").Copy Before:=ThisWorkbook.Worksheets("2023")
    closedBook.Sheets("MRL - In Production").Copy Before:=ThisWorkbook.Worksheets("2023")
    closedBook.Sheets("MRL - Completed").Copy Before:=ThisWorkbook.Worksheets("2023")
    
    closedBook.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub

 

[sspatriots]

To get around the problem, I ended up putting the following code in my "ThisWorkbook" module so it would still work when the worksheets from other workbooks were copied into the file.

I would still like to know if there is something I'm missing with the Microsoft comment in my previous post.

Function InRange(Range1 As Range, Range2 As Range) As Boolean

If Range1.Parent.Name = Range2.Parent.Name Then
    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
Else
    InRange = False
End If

End Function

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    If InRange(ActiveCell, Worksheets("COMM - In Production").Range("A:A")) Then
        Selection.Copy Destination:=Sheets("Jobs").Range("COMMJNRange")

    ReturnToJobsWSAfterCopyingCOMMJN

    End If
    
        
    If InRange(ActiveCell, Worksheets("COMM - Completed").Range("A:A")) Then
        Selection.Copy Destination:=Sheets("Jobs").Range("COMMJNRange")

    ReturnToJobsWSAfterCopyingCOMMJN

    End If
    
    
    If InRange(ActiveCell, Worksheets("MRL - In Production").Range("A:A")) Then
        Selection.Copy Destination:=Sheets("Jobs").Range("MRLJNRange")

    ReturnToJobsWSAfterCopyingMRLJN

    End If
    
    
    If InRange(ActiveCell, Worksheets("MRL - Completed").Range("A:A")) Then
        Selection.Copy Destination:=Sheets("Jobs").Range("MRLJNRange")
    
    ActiveSheet.ShowAllData
    
    ReturnToJobsWSAfterCopyingMRLJN

    End If


End Sub

 

[NdNoviceHlp]

Hi sspatriots. I'll relate my experience. I was trying to copy a ws and code from another wb to ThisWorbook but it would not work. What did work, was placing the sheet code in ThisWorkbook and before copying the new sheet, renaming the sheet with the code to "temp" and then copying the external sheet to ThisWorbook and renaming it to the original sheet name....and then deleting the "temp" sheet. Not sure if this is the same as the MS learning site, but it works. HTH. Dave

 

[Skyybot]

If you're only copying a Sheet, wouldn't only the code that is written in the Sheet be copied?

 

[sspatriots]

If you're only copying a Sheet, wouldn't only the code that is written in the Sheet be copied?

The code for the sheet did not come over with the code in my original post. The workaround in the second post does seem to be performing the way I wanted.

 

[NdNoviceHlp]

Thanks for posting your outcome. Have a nice day. Dave