Hello,
Can you please help me in making this code in a standard format, instead of
changing the code daily manually of the highlighted text. Thanks in Advance.
Sub macro1()
'
' Macro1 Macro
'
'
Dim Z As Long
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],RC[-5],RC[-1])"
Range("H2").Select
* Selection.AutoFill Destination:=Range("H2:H341")*
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("kmhpmebck1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("kmhpmebck1").Sort.SortFields.Add
Key:=Columns( _
"B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("kmhpmebck1").Sort
* .SetRange Range("A1:B334")*
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = False
Selection.Replace What:="total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1)),
TrailingMinusNumbers:= _
True
Range("A1").Select
ActiveCell.FormulaR1C1 = "Balance"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Portfolio"
Range("C1").Select
ActiveCell.FormulaR1C1 = "CCY"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-21
Application.CutCopyMode = False
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Columns("F:G").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Range("E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Selection.NumberFormat = "#,##0.00"
Range("D25").Select
Selection.End(xlDown).Select
Range("B2").Select
Selection.End(xlDown).Select
Z = ActiveCell.Row + 0
Range("B" & Z).Value = "Grand Total"
ActiveCell.Select
Selection.Font.Bold = True
Selection.End(xlUp).Select
Range("A1:D1").Select
Selection.Font.Bold = True
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub
Thanks,
Vebhav Jain
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
