Adding a macro to a saved file

[Paul]

Hi

I am generating a processing sheet for each state; then, the macro saves the states into a workbook for each branch, including the states which belong to that branch, using the following sample code

Sheets(Array("Branch 102 Totals", "AL", "FL", "GA", "MS", "102 Competition")).Cop
ChDir "V:\Branch and Corporate\BR102
ActiveWorkbook.SaveAs Filename:=
"V:\Branch and Corporate\BR102\Branch102BARModel.xls", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=Fals
Windows("BarModelv2.4.xls").Activat

1.) When the save completes, the macros in the "mother sheet" are no longer available. Can I incorporate them into the save logic selectively so that users will have access to them without needing to have the mother sheet? For instance, so they have access to only the macro "Profit modeling", but not any others
2.) Can the array save logic be expressed in a variable or does it need to be hardcoded
3.) Can I use the network path instead of the drive letter? I am afraid different users may have the drive mapped to a different letter and I would like them to be able to save the file back to the share drive so I can see the results

Thanks for the help! Pau

 

[Dave Peterson]

#1. You can use some of the techniques at Chip Pearson's site to copy code from
one workbook to another:
http://www.cpearson.com/excel/vbe.htm

But it might be simpler (if the macros that you share is always the same set (or
just one). Set up a template that contains that macro and a dummy worksheet
(name it Dummy).

Protect your code in the VBE to keep prying fingers away (if you want).

The copy the worksheets from your original workbook into a workbook based on
that template. (delete the dummy worksheet and you'll be set).

You could use an array variable to hold the names of the sheets to copy--or even
build it on the fly if you know the rules (and it varies).

And yes, you can use the UNC naming convention to save the new workbook.

Option Explicit
Sub testme()

Dim newWkbk As Workbook
Dim curWkbk As Workbook
Dim wks As Worksheet
Dim wCtr As Long

Dim mySheets() As String

Set curWkbk = ThisWorkbook
'Set curWkbk = Workbooks("barmodelv2.4.xls")

wCtr = 0
For Each wks In curWkbk.Worksheets
If Len(wks.Name) = 2 _
Or LCase(wks.Name) Like "*102*" Then
wCtr = wCtr + 1
ReDim Preserve mySheets(1 To wCtr)
mySheets(wCtr) = wks.Name
End If
Next wks

If wCtr = 0 Then
MsgBox "No worksheets to copy"
Exit Sub
End If

If Dir(curWkbk.Path & "\mytemplate.xlt") = "" Then
MsgBox "Template file is not available!" & vbLf & _
"Please contact Paul at ####."
Exit Sub
End If

Application.EnableEvents = False
Set newWkbk = Workbooks.Add(template:=curWkbk.Path & "\mytemplate.xlt")
Application.EnableEvents = True

curWkbk.Worksheets(mySheets).Copy _
after:=newWkbk.Worksheets(1)

Application.DisplayAlerts = False
newWkbk.Worksheets("dummy").Delete
Application.DisplayAlerts = True

newWkbk.SaveAs Filename:="\\server\sharename\path\Branch102BARModel.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

curWkbk.Activate

End Sub

If the sheet names never varied, you could use this instead of all that looping
junk:

Dim mySheets as Variant
.....later
mysheets = Array("Branch 102 Totals", "AL", "FL", "GA", "MS", "102 Competition")
.....later just copy as before.

 

[Paul]

Thanks a lot for your help! That was very useful.