Dear All,

I write a vba program to process a task and this program is sitting in
a workbook for each of the staff about 20 in the same department. Each
of them cannot see each other's workbook due to the nature of their
work but the process step is the same for all.
1) May I know how should I do to rollout the vba program to each of
their workbook? Do I need to do the import in the VBE?

2) they are using a workbook for each of the project. When they do the
subsequently project they need a new workbook to do the same process
again. If that is so may I know if I need to do the import all the
time when they use new workbook for new project? I cannot let them do
as they will access to the vba source.

3) there is a process where they need to transfer data from worksheets
to 1 particular worksheet to derive the subtotal. I created a toolbar
commandbutton to process. The problem is they need to go to the
specific or active sheet to process. If having 60 sheets then they
need to process 60 times. I do not want to put the button on sheets as
will create a lot of procedures.
Any other way to process by providing sheet number in inputbox and
process once for all the sheets(transfer data from all the source
sheets to a single destination sheet) What is the vba statement for
this?

Again, thanks for the assistance so far.

1) For the existing project since the excel files are already created on
each machine,
you can do import on each file.

2) For the next project, you can create a template having your VBE code in
it and circulate to
every one. For the next projects, they should use this template .

3) Instead of adding command button in a sheet, add a new button on the tool
bar,
and assing the macro to it. So the button will be always available to the
use on the tool bar.

Transfering data from all worksheets:

Below code gets a valid sheet no. from the user and transfers contents of
Range("A1") from every sheet (except the sheet for sheet no. entered ) in
to
the sheet for sheet no. entered, in to Column F.

Sub Test()
Dim getNum As String, mySheetNum As Integer
Dim myRange As Range, nCount As Integer
Dim totSheetNum As Integer

getSheetNum:
getNum = InputBox("Please enter the desitantion sheet number", _
"Sheet Numbar Required:")
If Len(getNum) = 0 Then Exit Sub
'i.e. if no data entered or user presses cancel, do noting.
If Not IsNumeric(getNum) Then
MsgBox "Please enter an Integer"
GoTo getSheetNum:
End If
'i.e. check if data entered is numeric or not if no ask again
If InStr(1, getNum, ".", vbTextCompare) 0 Then
MsgBox "Please enter an Integer"
GoTo getSheetNum:
End If
'i.e check if the number entered has a "." in it if yes ask
'for an integer again

mySheetNum = Int(getNum)
totSheetNum = ThisWorkbook.Sheets.Count
If mySheetNum totSheetNum Then
MsgBox "The sheet number you entered is not valid because" & _
" there are only " & totSheetNum & " sheets!" & Chr(13) & _
"Please enter a valid sheet number."
GoTo getSheetNum
End If

nCount = 1
For Each s In ThisWorkbook.Sheets
If Not s.Index = mySheetNum Then
Sheets(mySheetNum).Range("F" & nCount) = s.Range("A1")
nCount = nCount + 1
End If
Next

End Sub

"tango" wrote in message
om...
Dear All,

I write a vba program to process a task and this program is sitting in
a workbook for each of the staff about 20 in the same department. Each
of them cannot see each other's workbook due to the nature of their
work but the process step is the same for all.
1) May I know how should I do to rollout the vba program to each of
their workbook? Do I need to do the import in the VBE?

2) they are using a workbook for each of the project. When they do the
subsequently project they need a new workbook to do the same process
again. If that is so may I know if I need to do the import all the
time when they use new workbook for new project? I cannot let them do
as they will access to the vba source.

3) there is a process where they need to transfer data from worksheets
to 1 particular worksheet to derive the subtotal. I created a toolbar
commandbutton to process. The problem is they need to go to the
specific or active sheet to process. If having 60 sheets then they
need to process 60 times. I do not want to put the button on sheets as
will create a lot of procedures.
Any other way to process by providing sheet number in inputbox and
process once for all the sheets(transfer data from all the source
sheets to a single destination sheet) What is the vba statement for
this?

Again, thanks for the assistance so far.