Macro - Series of formulas in a loop to build tables

**See end of post for solution**

Hello all,

I have a macro that opens excel files and then loops through to copy/paste data to a worksheet called "formdata" (it copies column to column)

Now I have created a series of formulas that allow me to build a table for a set of data (a column of data) belonging to one worksheet. However since I will be opening an arbitrary number of worksheets (could be 1,2,x) I want to build as many tables as the files I open and I want them spaced equally between them.

Here is what I have put together so far:

Private Sub CreateStatsTables()

'Create the statistics tables macro. Created by Kostas02 04 2008
'
Application.ScreenUpdating = False

'Creating the table for Formdata Column A

ThisWorkbook.Worksheets("STATISTICS").Activate

' Clear the area where I will put the table
Range("C13:E32").Select
Selection.ClearContents


    Range("C13").Select
    Range(Selection, "C32").Select

' Here I select the data I have copy/pasted already through another formula and apply a frequency formula on them as well as some accumulation and percentages on seperate columns. The source data column is on a worksheet called Formdata. The destination worksheet for all tables is called Statistics.

    Selection.FormulaArray = _
        "=FREQUENCY(Formdata!C[-2],STATISTICS!RC[-1]:R[19]C[-1])"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("D14").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
    Range("D14").Select
    Selection.AutoFill Destination:=Range("D14:D32"), Type:=xlFillDefault
    Range("D14:D32").Select
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(RC[-2]:R[19]C[-2])"
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(R13C3:R32C3)"
    Range("E13").Select
    Selection.AutoFill Destination:=Range("E13:E32"), Type:=xlFillDefault
    Range("E13:E32").Select
    Range("A1").Select

' Graphs: Here I put some borders/formatting around the table, this is not necessary but it would be useful.

Range("A12:E32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A12:E12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select

Summing up:
What I am looking forward to achieve are 2 things.
a) have this procedure done automatically for all the number of columns available in the worksheet called "formdata"
b) space all tables equally between them (they are all of a certain size, 21 rows and 5 columns)
c) The formula only fills in 3 columns while the rest 2 are just text. How can I automate the text input (which is standard again) in the formula?

So far I have hardcoded the process for 10 tables but I need to limit it to however many I open each time. I think that I should do the loop some short of (I will try to explain the structure):

For each Column in Formdata
range set_number_of_rows_under_last_row_with_data
run formula
end with

Is this the correct idea?


All ideas welcome!

Cheers,

kostas


---Solution---

many thanks to Rylo for coming up with the following code

Sub CreateTables()
'create the base output tables
  Sheets("Statistics").Activate
  Range("B12:E12").Value = Array("Periods", "Number of Incidents", "Accumulative", "Percentage")
  Range("B12:E12").Font.Bold = True
  Range("A13:A32").Value = "Delay up to"
  Range("A34").Value = "Total number of incidents:"
  Range("B34").Formula = "=SUM(C13:C32)"
  Range("A34:B34").Font.Bold = True
  Range("B13").Value = "12:01:00 AM"
  Range("B14").Formula = "=b13+timevalue(""00:01:00"")"
  Range("B14").AutoFill Destination:=Range("B14:B32")
  Range("B13:B32").Value = Range("B13:B32").Value
  Range("C13:C32").FormulaArray = "=FREQUENCY(Formdata!$A:$A,STATISTICS!B13:B32)"
  
  Range("D13").Formula = "=C13"
  Range("D14").Formula = "=D13+C14"
  Range("D14").AutoFill Destination:=Range("D14:D32")
  Range("E13").Formula = "=D13/SUM(C$13:C$32)"
  Range("E13").AutoFill Destination:=Range("E13:E32")
  Range("E13:E32").NumberFormat = "0.00%"
  CreateBorders

  Range("A12:E34").Copy Destination:=Range("G12")
  Range("I13:I32").Replace what:="formdata", replacement:="Formdata2"
'copy the base tables to cover the number of output column instances
  With Sheets("Formdata") 'determine the last column of data in formdata
    lastcol = .Cells.Find(what:="*", after:=.Range("A1"), searchdirection:=xlPrevious, searchorder:=xlByColumns).Column
  End With
  
  For i = 1 To lastcol - 1 'the first column of data is covered by the initial table input
    Range("A12:K34").Copy Destination:=Cells(12 + i * 26, "A")
    newcol = WorksheetFunction.Substitute(Cells(1, i + 1).Address, "$1", "")
    Cells(12 + i * 26, "A").Resize(21, 15).Replace what:="$A:$A", replacement:=newcol & ":" & newcol
    
  Next i
  
  Range("A1").Select
End Sub



Sub CreateBorders()
    Range("A12:E32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A12:E12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

End Sub

You will find the necessary workbook attached in one of the following posts.

Regards,

kostas