Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you automate the process.
Regards - Dave.
Date: Tue, 7 Dec 2010 22:33:00 +0530
Subject: $$Excel-Macros$$ Error in Macro Code
From: vebhav.j...@gmail.com
To: excel-macros@googlegroups.com
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
--
----------------------------------------------------------------------------------
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
