Create Summary Table From Data Sheet

Status
Not open for further replies.

ir121973

Active Member
Joined
Feb 9, 2008
Messages
371
Hi, I wonder whether someone may be able to help me please.

Firstly, my sincere apoligies to @MickG, on this forum. I added a post to the site yesterday and @MickG gave me a brilliant solution here http://www.mrexcel.com/forum/excel-questions/664236-create-summary-table-data-sheet.html, but on reflection I've let this member down because I had forgotten some of my requirements, so I'm very sorry.

The solution that MickG helped me with works great, but in my original post, I had said the Source of the data came from 'Sheet1' and the paste range in the Destinantion sheet should be from rows A-I. Having looked at this today, I've realised that the Source sheet is called 'Combined ' and the paste range should be columns 'A', 'B', 'D', 'F', 'H', 'J', 'L', 'N', 'P', R', 'T', 'V', 'X', 'Z' and 'AD'.

I have already made some minor changes to the code because I needed to extend the copy range and change the Destination sheet name, and I thought it would be easy to make these other changes, but so far I've just not been able to get these to work.

This is the code I'm currently working with:

Code:
Sub MG13Oct10()
Dim Rng As Range, Dn As Range, n As Long
Dim Ac As Integer
Dim c As Long
    Set Rng = Range(Range("A4"), Range("A" & Rows.Count).End(xlUp))
        ReDim Ray(1 To Rng.Count, 1 To 15)
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        n = n + 1
        For Ac = 1 To 15
                If n = 1 And Ac > 2 And Ac < 15 Then
                    Ray(n, Ac) = MonthName(Month(Dn(, Ac)), True) & " Total"
                Else
                    Ray(n, Ac) = Dn(, Ac)
                End If
            Next Ac
        .Add Dn.Value, n
    Else
           For Ac = 3 To 8
                If Not .Item(Dn.Value) = 1 Then
                    Ray(.Item(Dn.Value), Ac) = Ray(.Item(Dn.Value), Ac) + Dn(, Ac).Value
                End If
            Next Ac
   End If
Next
c = .Count
End With
With Sheets("Resource Summary 12-13")
    .Range("A4").Resize(c, 15) = Ray
    .Range("A4").Resize(c, 15).Sort .Range("A4"), xlAscending
End With
MsgBox "Run!!"
End Sub

I'm very conscious and realise that members of this site have got better things to do than return to a previously 'solved' post, but I'm at a loss as to where I need to make these amendments. i just wondered whether someone may be able to take a look at this please and offer a little guidance on how I may be able to make these changes.

Many thanks and kind regards
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,909
Members
414,345
Latest member
Jonathan43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top