It will be very difficult to move the code to the personal.xlsb workbook without the initial file retaining some code. Can you explain why you want to do so ? Maybe there should be alternative solutions.
Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : dimanche 31 juillet 2011 04:27 À : Rajan_Verma; excel macro forum Objet : Fwd: $$Excel-Macros$$ Help needed to comine VBA modules Dear rajan verma, The worksheet 'analysis' is being created during the macro run of module "analysis" only ,Now naming the drilled down w.sheet is possible by including the following code in w.sheet analysis,I propose to run this from the personal macro workbook,How to achieve this?. kindly help Aju v chacko 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 ---------- Forwarded message ---------- From: Daniel <dcolarde...@free.fr> Date: Sun, Jul 31, 2011 at 12:53 AM Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules To: excel-macros@googlegroups.com I am sorry, I don’t know how to do it. Maybe it is possible to achieve it with a class module, but evenso, I’m not able to do it so far. Regards. Daniel De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la part de aju chacko Envoyé : samedi 30 juillet 2011 17:00 À : excel macro forum Objet : Re: $$Excel-Macros$$ Help needed to comine VBA modules 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 -- ---------------------------------------------------------------------------- ------ 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> http://exceldailytip.blogspot.com To post to this group, send email to <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link <http://www.facebook.com/discussexcel> 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
