Averaging Ranges across multiple workbooks

[ksmallfoot]

Hello! I am working on creating a program that will select a range of values from a separate workbook, take the average and then paste it on a new worksheet.

I have multiple columns of data for each workbook, and there are close to 100 different workbooks to analyze. So this code is important to shorten this daunting task!

I have a workbook called "Lab Program" that currently has code that allows me to select the workbook I want to choose (files with the data I need to analyze), we can call those "Test Data." After selecting Test Data, the program then transposes the data and then it is pasted on a new worksheet in the Test Data workbook. Below is that code:
___________________________________________
Public Sub Start()


Dim file As Variant
Dim csvWb As Workbook, newWs As Worksheet
Dim copyRange As Range

file = Application.GetOpenFilename(FileFilter:="Report Files *.csv (*.csv),*.csv", Title:="Please choose a .csv file to open", MultiSelect:=False)


If file <> False Then
Set csvWb = Workbooks.Open(file)
Set copyRange = csvWb.Worksheets(1).UsedRange
Set newWs = csvWb.Worksheets.Add(after:=csvWb.Worksheets(csvWb.Worksheets.Count))
copyRange.Copy
newWs.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If


End Sub
_______________________________________



Now, I honestly have no idea where to start to get the values I want. I would love to be able to use the AVERAGEIF function to treat all the zeros as outliers. I am not sure what the first step would be, I would love some help.


-K
Thanks

 

[NdNoviceHlp]

Maybe open the workbooks, get your average, store the average in an array. Close the wb. When done processing the files, unload the array into your wb. HTH. Dave