Dear daniel,
Thanks. Now it is working fine.But the sub '*
Worksheet_BeforeDoubleClick'* is included as worksheet level event routine
of w.sheet *analysis*.How can i incorporate this in personal macro
workbook.Once again thanking u for Ur help.
with regards
Aju v chacko
On Sat, Jul 30, 2011 at 6:33 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:
> Also add this Function to Remove Special Character From Sheet name ,Because
> if the Special Character include in the string it will not change the Sheet
> name,
> See the attached file..
>
> Public Function SheetName(Shname As String) As String
> Dim Cod As Integer
> Dim ShN As String
> For i = 1 To Len(Shname)
> Cod = Asc(Mid(Shname, i, 1))
> If (Cod > 64 And Cod < 91) Or (Cod > 96 And Cod <
> 123) Or (Cod > 79 And Cod < 90) Then
> ShN = ShN & Mid(Shname, i, 1)
> End If
> Next
> SheetName = ShN
> End Function
>
>
>
> -----Original Message-----
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Daniel
> Sent: Saturday, July 30, 2011 6:12 PM
> To: excel-macros@googlegroups.com
> Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules
>
> Here is my test file.
>
> -----Message d'origine-----
> De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> De
> la part de aju chacko Envoyé : samedi 30 juillet 2011 05:33 À :
> excel-macros@googlegroups.com Objet : Re: $$Excel-Macros$$ Help needed to
> comine VBA modules
>
> Dear daniel,
> Thanks for ur kind reply,But when i pasted this module into
> the module' analysis',the following line showing compilation error(red
> colour).error-("sub or function not defined").
>
> Set rg = Sheets("ANALYSIS").PivotTables(1).DataBodyRange
> >
> > If Intersect(Target, rg) Is Nothing Then Exit Sub
> If Target.Value <> "" Then
> >End If
>
> Kindly help
> Aju v chacko
>
>
> On 7/29/11, Daniel <dcolarde...@free.fr> wrote:
> > Paste the macro below in the ANALYSIS module :
> >
> >
> >
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> > As
> > Boolean)
> >
> > Dim rg As Range
> >
> > Set rg = Sheets("ANALYSIS").PivotTables(1).DataBodyRange
> >
> > If Intersect(Target, rg) Is Nothing Then Exit Sub
> >
> > Cancel = True
> >
> > If Target.Value <> "" Then
> >
> > Target.ShowDetail = True
> >
> > ActiveSheet.Name = Target.Offset(, -1).Value
> >
> > End If
> >
> > End Sub
> >
> >
> >
> > Daniel
> >
> >
> >
> > De : excel-macros@googlegroups.com
> > [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé
> > : vendredi 29 juillet 2011 16:25 À : excel macro forum Objet : Fwd:
> > $$Excel-Macros$$ Help needed to comine VBA modules
> >
> >
> >
> > Dear daniel,
> >
> >
> >
> > As u can see in w.sheet analysis contain a pivot table created by the
> > macro,There are two columns one is row labels (Final B Mr/S Batch
> > Apex..) & other sum of value field which is a actually a sum of
> > numeric field,It is possible to Drill down specific numeric filed,by
> > double clicking on it,Then a new sheet will be created with Drilled
> > down value,But the sheet name will be 'sheet2,sheet3...'.Instead of
> > such w.sheet name i want the name of w.sheet being created to be taken
> > from respective "row lable' field of the numeric value on which i
> > clicked.for ex:if clicked on value '403',the name of the sheet should
> > be Wound B.Hope it is clear & Thanking you in advance for the help.
> >
> > Regards
> >
> >
> > aju
> >
> > ---------- Forwarded message ----------
> > From: aju chacko <ajuvcha...@gmail.com>
> > Date: Thu, Jul 28, 2011 at 9:40 AM
> > Subject: Fwd: $$Excel-Macros$$ Help needed to comine VBA modules
> > To: excel-macros@googlegroups.com
> >
> >
> > ---------- Forwarded message ----------
> > From: aju chacko <ajuvcha...@gmail.com>
> > Date: Wed, 27 Jul 2011 11:37:44 +0100
> > Subject: $$Excel-Macros$$ Help to create project from the Vba modules
> > To: excel macro forum <excel-macros@googlegroups.com>
> >
> > Hi Friends,
> >
> > i wanted to drill down the pivotable(in w.sheet ANALYSIS) in such way
> > that the "Name of new worksheet" being created during drill down being
> > taken form the row label (Final b,Apex..etc) of pivot table on which
> > I clicked.Since I am novice to excel vba,i am not competent enough to
> > solve this issue.please see the attachement & Kindly help.
> >
> > With regards
> > aju v chacko
> > ajuvcha...@gmail.com
> >
> > --
> > ----------------------------------------------------------------------
> > ------
> > ------
> > 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/discussexcel
> >
> >
> >
> > --
> > ----------------------------------------------------------------------
> > ------
> > ------
> > 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/discussexcel
> >
> > --
> > ----------------------------------------------------------------------
> > ------------
> > 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/discussexcel
> >
>
> --
>
> ----------------------------------------------------------------------------
> ------
> 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/discussexcel
>
> --
>
> ----------------------------------------------------------------------------
> ------
> 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/discussexcel
>
> --
>
> ----------------------------------------------------------------------------------
> 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/discussexcel
>
--
----------------------------------------------------------------------------------
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/discussexcel
