I have a simple excel sheet (attached file) The file is sorted by date
and it shows information about deposits made in a day. Sometimes a deposit
might have more than 1 transaction, sometimes it is just one transaction.
The data is as follows: Date, Unit ID, Amount & Check number (there other
columns (B, D & F) which are hidden for formatting purposes.
I have a macro ( see if below ) which I run to group and format the data.
It simple does the following:
1) adds color to the heading, calculates a subtotal based on date, draws a
line at the end of each date and colors the cell where the subtotal
calculation is shown.
For most part the macro works well, except that when a day deposit has
just one item, it doesn't format the cells as when the deposit has more
than one item.
If you run the macro, you'll see that when the deposit has just one item,
the line to create an indication of "grouping items by day" does not work,
and also the subtotal cell is not highlighted in yellow like the others.
I would appreciate you suggestions and comments to see if I can have this
macro works as it is intended
Thank you a whole bunch
Here is the macro:
Sub Subs_T_Complete()
'
' Subs_T_Complete Macro
'
'
Sheets("Fixed").Select
Range("F1:F600").Select
Selection.Copy
Sheets("New").Select
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Fixed").Select
Range("H1:H600").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New").Select
Range("H1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("I9").Select
Application.CutCopyMode = False
Application.Run "PERSONAL.XLS!All_together"
Range("A2:G600").Select
Selection.Interior.ColorIndex = xlNone
Columns("H:H").Select
Selection.Style = "Comma"
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "2"
Range("B1").Select
Selection.Font.ColorIndex = 5
Range("D1").Select
Selection.Font.ColorIndex = 14
Selection.Font.ColorIndex = 5
Range("K2").Select
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 403.5,
13.5, _
240.75, 33#).Select
Selection.Characters.Text = "Subtotals listed by date"
With Selection.Characters(Start:=1, Length:=24).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("N5").Select
End Sub-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
sample_subtotal1.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
