ksmallfoot
New Member
- Joined
- Nov 27, 2018
- Messages
- 9
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
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