Linking summary

[dawsona]

I have an existing project report that has two lines per project - revenue and cost.

My boss wants a more detailed report which means that each project now has five lines of analysis.

The problem is that the existing report is wo be used as a summary. There are potentially 100 projects and I need a macro that will reference from the summary back to the detail.

I need a loop that starting at row 1 it will put in a formula in B1 that equals the sum of ("B1:B2") on the detail sheet then B2 will equal B5 on the detail sheet.

I will then use the offset command to move to the next row but am struggling to get it to pick up the data from the correct rows 7:9 and 12 in the first instance.

This needs to be done by way of formulas linking to the other sheet rather than pivot tables or similar solutions.

Any suggestions?

 

[Andrew Poulsom]

This seems to work:

Sub Test()
    Const Incr As Integer = 7
    Dim DetSh As Worksheet
    Dim DetRng As Range
    Dim SumSh As Worksheet
    Dim c As Range
    Dim Formula1 As String
    Dim Formula2 As String
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Set DetSh = Worksheets("Detail")
    Set DetRng = DetSh.Range("B1:B" & DetSh.Range("B65536").End(xlUp).Row)
    Set SumSh = Worksheets("Summary")
    x = 1
    y = 5
    z = 1
    Do
        Formula1 = "=SUM(Detail!B" & x & ":B" & x + 1 & ")"
        Formula2 = "=Detail!B" & y
        SumSh.Cells(z, 2).Formula = Formula1
        SumSh.Cells(z + 1, 2).Formula = Formula2
        x = x + Incr
        y = y + Incr
        z = z + 2
    Loop While z <= (DetRng.Rows.Count / Incr * 2)
End Sub

 

[dawsona]

Andrew

I have only just manged to find time to try out the code and it really is the "dog's whatsits!"

The example I gave wasn't quite true in terms of ranges but I can understand the code enough to manipulate to what I want.

Thanks again.