Hi Vebhav,
Is there any data after the range you mention? For example, if today's data is
Range A1:B330, is there any data in cells A331:B65000? If the cells below the
data is blank, then we should be able to help you. Also, the other highlighted
range, H2:H341; how does that relate to the first range, since it contains a
different number of cells? Is it always 11 cells more than the range of data in
Columns A & B?
Regards - Dave.
Date: Wed, 8 Dec 2010 13:47:33 +0530
Subject: Re: $$Excel-Macros$$ Error in Macro Code
From: vebhav.j...@gmail.com
To: excel-macros@googlegroups.com
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
--
----------------------------------------------------------------------------------
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
