Set a range to an input box on a template. Then have another macro use that range.

[dmenegr]

Hello All,

We have about 2000!! files that are in a table in word format, that we use to record data in our high volume lab. As you can imagine this is cumbersome system. What I have recently purposed to a customer is a excel workbook to be used throught out the entire process, from data gathering to the back end when engineers are plotting data. I made up a sample that has a template data entry sheet, with a macro to duplicate itself. Then 2 summary sheets that sort and the data by process and each data set, and a statistics for each page with average, Standard deviations,UCL, LCL, etc. Then finally another sheet which tracks the data and sorts it by month. This took a while to do and I don't want to repeat this process 2000 more times.

So what I'm looking to do it have a excel template made up, that on start up imports a user generated sheet as the data entry sheet. Then go through data entry sheet and have a user define each data set as a range per an input box. Then use those defined ranges in another macro to populate summary sheets, without having to reselect the range?

Is this possible?

Sub Auto_Open()
If ActiveWorkbook.Name = "E-MCL Template" Then
Set ActiveWorkbook.Name = Application.MsgBox(Prompt:="Enter File Name as MCL-XXXXX", Title:="Rename File", Type:=8)
End If
If MsgBox("Enter MCL Data Table Here", vbYesNo, "Reset") = vbYes Then
Sheets("Template").Select
End Sub


Sub DefineData()
Dim Rng(0 To 20) As Range, DataSetname(0 To 20) As String
Set DataSetname(0) = Application.InputBox(Prompt:="Enter Heading of first Data Set", Title:="Data Set 1 Title", Type:=8)
Set Rng(0) = Application.InputBox(Prompt:="Enter Range of first Data Set", Title:="First Data set Range", Type:=8)
Set DataSetname(1) = Application.InputBox(Prompt:="Enter Heading of second Data Set", Title:="Data Set 2 Title", Type:=8)
Set Rng(1) = Application.InputBox(Prompt:="Enter Range of first Data Set", Title:="Second Data set Range", Type:=8)
' Etc.
End Sub


Sub ImportData()
' Have this macro use the Ranges defined in "DefineData" to populate a summary sheet.
End Sub