Partial clean up of your macro
Sub Subs_T_Complete()
With Sheets("New")
Sheets("Fixed").Range("F1:F600").Copy
.Range("F1").PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Fixed").Range("H1:H600").Copy
.Range("H1").PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Sheets("New").Select
Range("I9").Select
Application.Run "PERSONAL.XLS!All_together"
Range("A2:G600").Interior.ColorIndex = xlNone
Columns("H").Style = "Comma"
Range("B1") = 1
Range("D1") = 2
Range("B1,d1").Font.ColorIndex = 5
Range("K2").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
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com
From: CAN
Sent: Tuesday, July 31, 2012 9:33 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Macro is not doing the right formatting - need
help
Thank you Don, you are correct. I should provide a more clear explanation of
what I need.
I have attached 2 additional files that shows what happens when I run the
original macro shown on this emai (file_after_Original_macro.xksx)l. You will
see that not all the cells are formatted the same. The second file
(file_the_way_it_Should_be.xlsx) shows the way I need the macro to works, with
the cells formatted correctly.
I hope this helps.
Once again, Thank you a whole bunch!!
On Tuesday, July 31, 2012 9:52:01 AM UTC-4, Don Guillett wrote:
You should, at least provide an .xlsM file with your macro and before/after
examples of what you want. Why should we do extra work?
I will then be happy to have a look.
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com
From: CAN
Sent: Tuesday, July 31, 2012 8:17 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Macro is not doing the right formatting - need help
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
--
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
--
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
