Need Help combining worksheets into a summary.

[deewilly]

I need to have data gathered by several different users (approximately 100) using the same worksheet that includes totals and percentages. After the information is gathered I need to combine them into a summary page. Can anyone tell me to the best way to accomplish this?

 

[tactps]

If you have the same worksheet being used by everyone, you should be able to copy and paste values from one worksheet to another.

If you post some more detail, someone will surely help you.

 

[deewilly]

I'm planning on sending a formatted workbook (only 1 sheet) out via e-mail to several managers. The workbook will include several columns and rows (columns of vendors we used, rows of equipment we buy from each). The columns wil be totalled and have a percentage of the total equipment in each column also. Each of the managers will be returning these workbooks to me via e-mail and I'd like to know if there is an easy way to import the their totals into a summary, without having to cut and past.

 

[tactps]

How about detaching their worksheets into a folder (e.g. c:\junk), naming them 1.xls, 2.xls, etc, and then running a macro like this:

Sub macro10()
Dim counter As Integer
ChDir "c:\junk"
counter = 1
Do Until counter = 100
With Application.FileSearch
.NewSearch
.LookIn = "c:\junk"
.SearchSubFolders = False
.Filename = counter & ".xls"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() <> 0 Then
On Error GoTo Skip
Workbooks.Open Filename:="C:\junk\" & counter & ".xls"
[b65536].End(xlUp).Select
Range(ActiveCell, ActiveCell.Offset(0, 1)).Copy
Windows(2).Activate
[b65536].End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
Windows(2).Close
End If
Skip:
counter = counter + 1

End With
Loop
End Sub