Solved: Input Data and Output Results

[MrRhodes2004]

TxDOT has produced a spreadsheet that calculates four values for the use in the design of their bridges. It is a good spreadsheet to use for the most part and has been verified. The problem is that the agency has locked down the file so that it cannot be effectively be used.
Typically, you need these factors for multiple bridges that have different cases, and situations. The file does not allow the copying of the sheet for different cases. The only thing that you can do is enter the information, write down the factors, and then enter the next case. A VERY tedious process. There are also other items in the spreadsheet that resets themselves at the change of information. If you forget to check that, your information may be wrong.
I would like to have a file that has all the different cases as user input then a results area where the four pieces of data are collected. There is a sample of what I am looking for and the distribution_factors.xls file.
I do not want to change their file. I just want to be able to make it more usable. Any ideas?

[mdmackillop]

Hi MrR
Try the following. It requires both workbooks to be open and is to be run from the sheet containing your data
[vba]
Sub GetData()
Dim wsData As Worksheet
Dim wsDistF As Worksheet
Dim Results As Range, Tgt As Range
Dim i As Long
Set wsData = ActiveSheet
Set Tgt = Workbooks("distribution_factors.xls").Sheets("Dist. Factors").Range("E11")
Set wsDistF = Workbooks("distribution_factors.xls").Sheets("Dist. Factors")
Application.ScreenUpdating = False
wsDistF.Activate
For i = 5 To wsData.Range("B5").End(xlDown).Row
wsData.Cells(i, 2).Resize(, 8).Copy
Tgt.PasteSpecial Paste:=xlValues, Transpose:=True
Set Results = wsData.Cells(i, 10).Resize(, 4)
Results(1) = Range("I32")
Results(2) = Range("I34")
Results(3) = Range("I36")
Results(4) = Range("I38")
Next
Application.CutCopyMode = False
wsData.Activate
Application.ScreenUpdating = True
End Sub
[/vba]

[MrRhodes2004]

MD,

Thank you very much. Now I understand a little more how to pass information between sheets. Again, I am glad I asked my question on here.

Michael