Moving a sheet module from one workbook to another

[bobaftt]

I have written a macro to format a spreadsheet and then copy the formatted information into a new spreadsheet and close the original spreadsheet. The problem I have is that I have some code written to highlight a row a certain color based on a data validation list. I need to get that code into the new sheet so that it will work after the macro is closed.
Here is the code I have found/put together to try and move the module. It adds the module as a class module instead of a sheet module.

Sub CopyModule()

Dim FName As String
With Workbooks("NewCCPFormatSuspenseUpdate.xlsm")
FName = .Path & "code.txt"
.VBProject.VBComponents("FSuspense").Export FName
End With
Workbooks("Book1").VBProject.VBComponents.Import FName
End Sub

Here is the code I am trying to move:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column <> 25 Then Exit Sub
        
        Select Case .Value
            'Green
            Case "Backup in Account Folder", "Backup in EDS"
                .EntireRow.Interior.ColorIndex = 43
            'Purple
            Case "Paid", "Transfer in Process"
                .EntireRow.Interior.ColorIndex = 39
            'Blue
            Case "Need Prior Bill’s Info/Backup", "Need Backup-File Doesn’t Match"
                .EntireRow.Interior.ColorIndex = 41
            'Tan
            Case "Admin Services-Request Elec B/U", "Admin Services-Format Elec B/U", "Admin Services-Need Backup-Missing/No Info"
                .EntireRow.Interior.ColorIndex = 40
            'Yellow
            Case "Due Date Error", "Greater than 20% Disc", "Working with BC", "No Outstanding Bill"
                .EntireRow.Interior.ColorIndex = 27
            'Grey
            Case "Holding for Additional Files/Prem"
                .EntireRow.Interior.ColorIndex = 16
            'Orange
            Case "BC to Clear"
                .EntireRow.Interior.ColorIndex = 45
                
                End Select
    End With
End Sub

[Kelshaer]

Export it to a .bas rather than .txt

[Domski]

You could just copy the sheet and have it as the basis for your new workbook.

Dom