Excel 2007 VBA: how to build a table from various spreadsheets?

[zeno]

Hello,

I'm starting out on VBA and have been looking for a solution online, but have not found what can help my question.
I try to build a table with various formulas in Excel 2007 using VBA. The base data are in 2 different spreadsheets and I will build the table in a 3rd sheet of the same file.
In my sample case, I use about 10 rows even though later on that number will increase significantly. I give an example table below.

Method:

Input 1) ColumnA and ColumnB in Sheet1: both have 10 rows of numbers and a column title in row 1. I will choose data from 1 of both rows (by selecting ColumnA or ColumnB?).

Input 2) ColumnA in Sheet2 (next sheet): 3 constants

Output 1) ColumnA through ColumnE in Sheet3 (some other sheet): 10 rows of calculation using input 1 and input 2, with each column of output 1 using a different calculation method. I will alternatively choose ColumnA or ColumnB from Sheet1 for the calculations by choosing its corresponding title heading.

I appreciate your help. Thank you.

example of table construction (S=sheet):

ColumnA(S1) ColumnB(S1) ColumnA(S2) ColumnA(S3) ColumnB(S3) ColumnC(S3) ColumnD(S3)

Title A | Title B | Constants | Title C | Title D | Title E | Title F
12.1 | 4.9 | 4
43.8 | 7.6 | 7 | Average(A2:A3)
8.7 | 5.9 | 5 | Average(A3:A4) | Average(A2:A4) | formula3 | formula4
6.5 | 15.8 | x | Average(A4:A5) | Average(A3:A5) | formula3 | formula4
7.4 | 25.7 | x | Average(A5:A6) | Average(A4:A6) | formula3 | formula4

Thank you for your help on VBA code to create sheet3.

 

[cjcobra]

Have you tried recording the macro instead of writing it from scratch? then edit the parts needed.

 

[zeno]

By recording part of the macro, I received the following code below. However, the length of the columns varies (up to 5000 rows) and how can I integrate that?

Option Explicit

Sub ATestrecorder()
'
' ATestrecorder Macro
' record macro

    ActiveCell.FormulaR1C1 = "=RC[-8]"
    Range("I1").Select
    Selection.AutoFill Destination:=Range("I1:N1"), Type:=xlFillDefault
    Range("I1:N1").Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(Datatest!RC[-8]:R[1]C[-8])"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K11"), Type:=xlFillDefault
    Range("K2:K11").Select
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(Datatest!R[-1]C[-9]:R[1]C[-9])"
    Range("L3").Select
    Selection.AutoFill Destination:=Range("L3:L11"), Type:=xlFillDefault
    Range("L3:L11").Select
End Sub

 

[cjcobra]

This will select everything below the first cell until it hits a blank.

Range(ActiveCell, ActiveCell.End(xlDown)).Select

 

[zeno]

Thank you for your reply.
How can I write a series of titles? e.g. A1:D1 has titles "date", "time", average(2) and average(3) respectively?
And next, how can I get to a specific cell ? With the recorder, a specific cell got selected. But instead I'd like the vba to go to those specific cells.
So in column A and B it copies the last cell of an average. In columns C and D I calculate the averages, for which the recorder wrote the syntax already.
Can you please include the whole vba, including what I wrote before.
Thank you.