copy a worksheet and function

[colinharwood]

Hi
After copying a worksheet which contains a function to a new workbook, the function disappears from the worksheet, is there a way to overcome this.
Thanks a lot

This is the worksheet code:

Private Sub Worksheet_Activate()
ActiveWindow.SmallScroll Down:=-96
Range("A3").Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Cells(ActiveCell.Row, 1).Resize(1, 21).Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
rowOffset = -4



End Sub

Function AddSpace(Str As String) As String
Dim i As Long
For i = 1 To Len(Str)
AddSpace = AddSpace & Mid(Str, i, 1) & " "
Next i
AddSpace = Trim(AddSpace)
End Function

 

[Joe4]

If you are just copying the sheet to a new workbook, only that data on the sheet is copied over. VBA code is NOT copied over.
Why not make a copy of the file itself? Then the new file will have all of that in there. If there are pages you don't need, just delete them after the file copy.
You code do all of that with VBA code (do a file copy and delete sheets).

 

[smitpau]

Is it possible you could make the function into a Macro and copy the Module over to the other workbook, may not be the ideal solution but should work.

 

[colinharwood]

Thanks for the suggestions.
I dont think that there is any way to save it as a Macro, so I'll look into saving whole file and then deleting sheets
Thanks

 

[Fluff]

Are you copying the entire sheet, or just the cells?
Also is the other workbook an xlsm workbook?