How Can I Speed Up Windows?: How Can You Fix - Building a Report using a VBA Macro in Excel?

Building a Report using a VBA Macro in Excel

My boss has asked me to produce a report for him in Excel.
(It really should be in Access but he wants it in excel so I'm stuck doing it this way).
I have to do this everyweek and want to simplify the process.
I start with an excel sheet of data, that has the same colomns in the same order every time so cleaning up the data is pretty straight forward.
But then I have to break the data into subsets, put the same headers on the top of each subset, then subtotal
each subset.
The subsets will be different sizes and with different numbers of them each time so this part of the process is the variable I need to deal with.
Can I have some suggestions on how I might accomplish this? I'm thinking I'd start with a "Report Generating Spreadsheet" and use a macro in that workbook to produce the "Report Spreadsheet".
Any suggestions?

Keys to the Problem Building a Report using a VBA Macro in Excel

Download Error Fixer (Free)

Hi,
I guess this involves reading down a column and collecting grouped data together and then copying it to another location.
The macro below makes a start on that.
This line
V = Split("AAA,BBB,CCC", ",")
Contains the groups and instead of AAA etc they could be company names or whatever you like, you can add to the list but maintain the syntax.

The macro takes each of these in turn and reads down column A of sheet1 and at the end of each loop copies all like date which can then be pasted somewhere else.
Paste this test data into a1 of sheet 1.
I have included a command to stop the macro at the end of each loop so you can see what's happening

aaa
me
£999
formula

aaa
me
£1,000
formula

bbb
me
£1,001
formula

aaa
me
£1,002
formula

bbb
me
£1,003
formula

ccc
me
£1,004
formula

ccc
me
£1,005
formula

Sub Split_Sheet()
Dim MyRange, x As Long, LastRow As Long
Dim CopyRange As Range
Set sht = Sheets("Sheet1")
LastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = sht.Range("A1:A" & LastRow)
V = Split("AAA,BBB,CCC", ",")
For x = 0 To UBound(V)
    For Each c In MyRange
        If UCase(c.Value) = V(x) Then
            If CopyRange Is Nothing Then
                Set CopyRange = c.EntireRow
            Else
                Set CopyRange = Union(CopyRange, c.EntireRow)
            End If
        End If
        If Not CopyRange Is Nothing Then
            CopyRange.Copy
            'Do something with data
        End If

    Next
stop
    Set CopyRange = Nothing
Next
End Sub

If this post answers your question, please mark it as the Answer.
Mike H

To install drivers and other optional updates

To install optional updates, you need to review and then select them from the list of updates that Windows finds for your computer. Optional updates are not installed automatically.
Open Windows Update by clicking the Start button Picture of the Start button, clicking All Programs, and then clicking Windows Update.
In the left pane, click Check for updates, and then wait while Windows looks for the latest updates for your computer.
If any updates are found, click View available updates.
Select the optional updates that you want, and then click Install. Administrator permission required If you are prompted for an administrator password or confirmation, type the password or provide confirmation.