VBA - Insert data as a "title" for a grouping of data

[lheisner]

To all you excel gurus, thank you for taking the time to look at this call for help!

I've managed to hammer out every other problem I have had with this sheet, but I don't even know where to start with this particular issue (note, my experience with VBA has been minimal before now). What I'm looking to do is to take a group of data's subtotal description line (ie. "Group A Total", but not Group A's actual subtotal) and place it at the top of the grouping of data.

Because I'm sure that sounded confusing (it confused me when I first started doing these reports and they asked about it!), here's what I have

Group Name	Item No	Item Desc.	In Stock	Value
Packaging	Wrap	Plastic Wrap	45	$39
Packaging	Box	Boxes	52	$10
Packaging Total			Total	$49
Group Name	Item No	Item Desc	In Stock	Value
Elements	Zinc	Zi	1	$100
Elements	Oxygen	O	25	$150
Elements	Carbon	C	5	$50
Elements Total			Total	$300

<tbody>
</tbody>

What I need is instead is something that looks like:

		Packaging Total
Group Name	Item No.	Item Desc	In Stock	Value
Packaging	Wrap	Plastic Wrap	45	$39
Packaging	Box	Boxes	32	$10
			Total	$49

<tbody>
</tbody>

So, if this were to be at the top, A5 above C1.

The problem, of course, is that none of my categories are static (some are 12 items long, others are 2), and that sometimes there are 12 categories, and sometimes there are 20. Any help would be appreciated!

 

[RudiS]

There is no feature in Excel that can do that specifically, so (as you mentioned) you will need to resort to VBA for that. To write the code we would need to have a bit more detail on the structure and layout of your source sheet, or can we go by the simplified example you posted above?

 

[lheisner]

I figured we would probably have to resort to VBA. The two table examples above are what I have and what I need, respectively - the top table more or less just needs to have the subtotal label put to the top of the group.

As a side note, there are no formulas that need to be preserved in this - it's just the data and how the boss wants it to look, if that helps.

 

[RudiS]

So if I take the top table in your first post and use that as the basis of the macro (to add the label at the top), that would be a decent reflection of your current data, barring the fact that the record counts differ between the tables? Also, do you still need to maintain a single row split between the tables since the label will be placed in the blank row between the tables above Item Desc.?

 

[lheisner]

Ah, alright, now I see where I was probably confusing - The first example has two categories whereas the second only has one. I only put the 2nd in to illustrate generally what I see - I have around 12-24 "groupings" of data, with each looking (individually) similar to table 2.


To answer you question, though, yes, that's pretty much what I need - I do not, though, need an extra line in between. The File would end up looking similar to this:

		Packaging Total
Group Name	Item No	Item Desc.	In Stock	Value
Packaging	Wrap	Plastic Wrap	45	$39
Packaging	Box	Boxes	52	$10
			Total	$49
		Elements Total
Group Name	Item No	Item Desc	In Stock	Value
Elements	Zinc	Zi	1	$100
Elements	Oxygen	O	25	$150
Elements	Carbon	C	5	$50
			Total	$300

<tbody>
</tbody>

Etc.

Pretty much as long as each grouping has the "(Category) Total" in the top middle, it will work just fine

 

[RudiS]

OK... I can set something up, but right now I'm going to bed! Need to be up early tomorrow!
If no one else has helped by the time I get back here, I'll provide some code to do this based on your example!

Cheers

 

[lheisner]

Very appreciated, thank you very much! Rest well!

 

[RudiS]

Try this code...

Sub MoveLabelToTop()
Dim rF As Range
    Application.ScreenUpdating = False
    With Range("A:A")
        Set rF = .Find(What:="Total", LookAt:=xlPart, SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, MatchCase:=False)
        If Not rF Is Nothing Then
            Do
                rF.End(xlUp).EntireRow.Insert
                rF.Copy rF.End(xlUp).Offset(-1, 2)
                rF.Clear
                Set rF = .FindNext(After:=rF)
            Loop While Not rF Is Nothing
        End If
    End With
    Application.ScreenUpdating = True
    MsgBox "All labels moved above the group.", vbInformation
End Sub

NOTE:
The code is written to accommodate for the data structure as illustrated in your opening post.
If the group structures of your actual file are different, you will need to modify the code to adjust for this!

 

[lheisner]

The macro works perfectly! Thank you so much, you've saved me a fair amount of time and possibly from getting carpal tunnel, haha!

I knew there had to be a reliable way to do it, but after banging my head against it for as long as I did, I knew asking for help was the best way. Once again, really appreciated!

 

[RudiS]

Excellent...tx for the feedback