Dear daniel,
Actually the raw data for worksheet "*ANALYSIS*" was
downloaded from *SAP -ERP *,With help of macro it is consolidated into
a*pivot table
* in worksheet *analysis"*.Every day i have to do this process many
times,That's why i propose to include in Personal macro workbook,so that
with a single click it can done
Regards
aju v chacko
---------- Forwarded message ----------
From: Daniel <dcolarde...@free.fr>
Date: Sun, Jul 31, 2011 at 1:57 PM
Subject: RE: $$Excel-Macros$$ Help needed to comine VBA modules
To: excel-macros@googlegroups.com
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
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
