Copying active worksheet to new workbook with module to go

[andytpl]

I a workbook that includes about 50 odd worksheets and with a few modules as well. I have to on a regular basis create a new worbook for some of these worksheets and after doing that manually import the relevant modules to these new workbooks. It would be fantastic if the above process can be done more efficiently. I reckon what I love to see being accomplice is using VBA to automate the whole process. The codes should allow the selection of the worksheet or worksheets to create into new workbooks with the same name as the worksheets and then allow selection of the modules to accompany these new workbooks.

Appreciate any help

[geekgirlau]

Hi Andy, and welcome to the Board!

This requires that you have trusted access to the code module; not usually a good idea. What about reversing the process - save the workbook with a new name and delete the sheets that you no longer want?

[andytpl]

Understand the concern, but your suggestion entails certain risks such as wrong deletion of worksheet and because of the number of worksheets involved this become too tedious.
I tried using the recorder but this don't work when module is involved.

[geekgirlau]

Sure a macro that deletes the worksheets that you don't want is no more difficult than defining the worksheets that you DO want ...

[andytpl]

That not really what I am after. I need a macro that allows me to copy the active worksheet or selection of worksheets together with the modules in the active workbook to new workbook/workbooks. I hope I make myself clear.

[Norie]

I don't see what the problem is with geekgirl's suggestion.

Sure you can write code to import modules, write code etc but it can raise certain issues.

One of those issues can be security.

[rory]

Since you seem quite determined ( :) ), this is the sort of code you want:
Function CopyVBEComponent(vbpSourceProject As VBide.VBProject, _
vbpDestProject As VBide.VBProject, _
strComponent As String) As Boolean
' Copy VBE component to another workbook's project.
On Error GoTo err_handle
Dim vbcExport As VBComponent
Dim strFileName As String
' Return reference to component to export.
Set vbcExport = vbpSourceProject.VBComponents(strComponent)
' Construct name for exported file.
strFileName = ActiveWorkbook.Path & "\" & vbcExport.Name
' Export component from exporting project.
vbcExport.Export strFileName
' Check whether component already exists in project.
If ComponentExistsInProject(vbpDestProject, strComponent) = False Then
' Import component into project.
vbpDestProject.VBComponents.Import strFileName
Else
With vbpDestProject.VBComponents
' Remove existing copy and import fresh one
.Remove .Item(vbcExport.Name)
.Import strFileName
End With
End If
CopyVBEComponent = True

clean_up:
On Error Resume Next
Set vbcExport = Nothing
' Delete file.
Kill strFileName
Kill strFileName & ".frx"
Exit Function

err_handle:
Debug.Print Err.Number & " : " & Err.Description
CopyVBEComponent = False
Resume clean_up
End Function
Function ComponentExistsInProject(vbpProj As Object, _
strComponentName As String) As Boolean
Dim vbcTest As Object

On Error Resume Next
' Check whether component already exists in project.
' Return True if it does exist.
Set vbcTest = vbpProj.VBComponents(strComponentName)
If Err <> 0 Then
Err.Clear
On Error GoTo 0
ComponentExistsInProject = False
Else
ComponentExistsInProject = True
End If
End Function


It requires a reference to the VBA Extensibility library, but could easily be rewritten to use late binding.

HTH
Rory

[andytpl]

Norie,

It is for security reason that raised the need to create individual workbook from the worksheets in the primary workbook. The primary workbook is a compilation of sensitive material from various departments. These department need to know and work on those worksheets related to them but not those belonging to other departments. This primary workbook is used to colate data from all the departments in the company. These workbooks after being updated are then copied back to the primary workbook on a regular basis.
The modules in this primary workbook are needed by each of the worksheet being copied to become individual workbook and for this particular reason that I need this solution. If it been a straight creation of individual workbook from the worksheet I wouldn't need to post this question.

[andytpl]

roxy,

Your suggestion is beyond my limited understanding of VBA. I believe these are function macro, I don't understand how they work with reference to my need. What I wanted to acheive is that whenever I copy a worksheet to become individual new workbook (preferrably of the same name as the worksheet)the modules in the original workbook is also exported out to this newly created workbook.

[Charlize]

- you can use activesheet.name to save under the new workbook
- you store the name of the sheet somewhere (or use the name of the new workbook without xls and delete every sheet that hasn't the same name.
- because you saved your workbook to a new workbook, the modules are also available

I think that this way is a little easier to grasp than copying modules.

[andytpl]

Charlize,

Thanks for the suggestion, it is exactly what I was thinking but I always like to know if this procedure can be done with VBA. This mean deleting all other sheet in this workbook and then saving the active sheet as a new workbook of the same name as the active sheet.

[geekgirlau]

Andy, I know you're not fond of my suggestion but try the attached - run the macro "CreateWBCopy" and see what happens.

[andytpl]

Tried your attached file but got an error message, "Run time error 1004".

[andytpl]

I have this code and it seem to work but one problem I have is that I must make all the hiden worksheets visible. This is not my intent because I need to have some worksheets hidden. If this is the case how do I need to change the code to make it work the way I want

Sub exportwksht()
Dim myPath As String, NewName As String, ws As Worksheet
myPath = ThisWorkbook.Path
NewName = ActiveSheet.Name
Application.DisplayAlerts = False
For Each ws In Sheets
ws.Visible = True
If ws.Name <> NewName Then ws.Delete
Next
ThisWorkbook.SaveAs NewName & ".xls"
End Sub

[rory]

If you want to keep the hidden worksheets in the new workbook, don't delete or unhide them. If you don't want to change the original, you might want to do the SaveAs first so that all the changes you make are to the saved copy.
Regards,
Rory

[Norie]

andy

I don't think you understand what I meant by security, but then again perhaps I wasn't clear, I was talking about macro security.

By the way it still isn't 100% clear what you actually want to do.

And I'm not suprised all the sheets become visible.

ws.Visible = True

[Charlize]

Better set the displayalerts back to true. Just a quick tip.

[Charlize]

I have this code and it seem to work but one problem I have is that I must make all the hiden worksheets visible. This is not my intent because I need to have some worksheets hidden. If this is the case how do I need to change the code to make it work the way I want

Sub exportwksht()
Dim myPath As String, NewName As String, ws As Worksheet
myPath = ThisWorkbook.Path
NewName = ActiveSheet.Name
Application.DisplayAlerts = False
For Each ws In Sheets
On error resume next
If ws.Name <> NewName Then ws.Delete
Next ws
On error goto 0
Application.DisplayAlerts = True
ThisWorkbook.SaveAs NewName & ".xls"
End Sub

[andytpl]

Guys and Gals,

Thanks for all your help. Charlize modification made the whole thing works the way I wanted.
I am still trying to get a grip of VBA and forum like this are giving me lots of help. Utlimately I hope to learn and accummulate enough knowledge to be in a position to help others. Will appreciate your suggestion of good read on VBA to get a leg up.

[andytpl]

Having solved the problem of creating a workbook with the module suggested by Charlize.
The next problem I am facing right now is I want to launch this macro by creating a command button of the toolbars so that this macro is independant of the workbook where I am trying to create new workbook from the active worksheet. I tried placing this macro module in the Personal.xls file but somehow when I launch the macro and this thing took place. The visible worksheets are deleted expect the active worksheet which is fine, but the filename is that of the original workbook instead of the activeworksheet and this create a problem. Secondly the command button macro is now associated with this workbook instead of the personal.xls.
Any idea what went wrong and how can I overcome it.