I want the highlighed code to be in a standard format. The data which i get
daily it keep varies and i get struck at range(A1:B334). when i recorded
this macro at that time it was A1 :B334
and next day it changed to A1:B321, Next day i ran the macro...not able to
execute.i hope you understood my query.
On Wed, Dec 8, 2010 at 9:45 AM, Dave Bonallack <davebonall...@hotmail.com>wrote:
> 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: Wed, 8 Dec 2010 06:51:01 +0530
> Subject: Re: $$Excel-Macros$$ Error in Macro Code
>
> From: vebhav.j...@gmail.com
> To: excel-macros@googlegroups.com
>
> Can someone look into this and suggest me.
>
> On Tue, Dec 7, 2010 at 10:33 PM, vebhav jain <vebhav.j...@gmail.com>wrote:
>
> 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
>
> --
>
> ----------------------------------------------------------------------------------
> 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
