Re: $$Excel-Macros$$ Introduce Yourself !!
Dear fellow members, My name is Aju v chacko (41),I am working in MRF TYRES,Kottayam,Kerala as SAP coordinator.I have moderate knowledge in excel macros I received great help from this community.My main hobby is reading I have created a RECONCILIATION TOOL in using VBA,I will share it you within few days. Thank You Aju V Chacko On Sat, Jun 9, 2012 at 12:51 AM, Ayush Jain jainayus...@gmail.com wrote: Hey all new and current posters, Welcome to excel group,one of the largest online community of excel Fans! I hope you enjoy your time here find this forum to be a friendly and knowledgeable community. Please feel free to post a small introduction, a friendly hello or tell us a bit about yourself. Why not tell us where are you from, what you do, what your interests are, how old you are, which is your favourite excel site or blog is or anything else that comes to mind! Thanks for your time Ayush Jain Group Manager Microsoft MVP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ How to create a double click using VBA CODE without actually double clicking on the mouse
Dear experts, Kindly help me with vba code for the following problem ,I have created a macro *that trap the event DOUBLE CLICKING* of mouse,But i want to optimize in such a way that the *user need not double click each time with the mouse*,Instead I want to create *double click of mouse using VBA CODE*,so when this code *create double click event* ,the code written for this event will get fired. Thanking you in advance Aju V Chacko -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
dear sam, Thanks for your kindness.I have imported all the module as u mentioned,But the following line in class module is throwing an error(syntax error) ' If Not IsEmpty(ActiveCell) Then' But i didn't see anything wrong with this line.By the way i am using excel 2007 and my purpose is to shuttle data between 'Excel' and 'SAP' applications.My purpose is to copy data from the cell once I select Excel window , Now i am running this macro by selecting excel window then pre the short cut key assigned to the macro for copying the data every time.Kindly help me.Thanking you in advance aju v chacko On 9/26/11, Sam Mathai Chacko samde...@gmail.com wrote: This is how you do it. Code below 'In Module Option Explicit Dim objApp As New Class1 Sub Auto_Open() Set objApp.app = Application End Sub Sub Auto_Close() Set objApp = Nothing End Sub Sub startappmon() If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If ActiveCell.Value Is Not Empty Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If End Sub 'In Class (name of class here is Class1) Option Explicit Public WithEvents app As Application Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If Not IsEmpty(ActiveCell) Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If End Sub Regards, Sam On Mon, Sep 26, 2011 at 10:33 AM, aju chacko ajuvcha...@gmail.com wrote: Dear friends, I have created the following code to to trap windowactivate event and execute a code from personal macro workbook when the activate sheet name is sap vs phy.But it works only when the excel is opened for first time.Kindly check the code give necessary correction so that once maco is executed whenever excel window is activated w.shhet name is sap vs phy then the following code is executed If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If ActiveCell.Value Is Not Empty Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If The sequence in which i made the modules are as follows . '***macro in personal macro workbook Sub exceltosap() ' ' Macro1 Macro ' startappmon End Sub ... '***macro in pesonal macro workbook Dim x As New Class11 Sub startappmon() Set x.app = Application End Sub .. '***class module in personal macro workbook Public WithEvents app As Application Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If ActiveCell.Value Is Not Empty Then Selection.Copy
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
Dear sam/paul Isempty(activecell.value) is OK now.But the macro doesn't work as i expected,It triggers only once.Actually what i want is whenever I switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro should execute. As i have understood,the *workbook_activate *event triggers only when we are switching between excel windows.Kindly help Aju v chacko On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko samde...@gmail.comwrote: It shouldn't be throwing an error, unless there is no workbook at all. There are lots of ways to check whether a cell is empty. Try this. If Len(ActiveCell.Value)=0 Then OR If ActiveCell.Value= Then Paul, the event that Aju is after, is for an add-in to identify whenever a workbook with the mentioned sheet name is activated at an application level. The event which you mentioned is at the Workbook level (associated with the file that it is written in). Aju requires the event at an application level. Let me know if you need further clarification. Regards Sam Mathai Chacko On Mon, Sep 26, 2011 at 4:42 PM, Paul Schreiner schreiner_p...@att.netwrote: Forgive me if this is totally off-base. But I have never used the Activate event in a Class module. In the ThisWorkbook module, I would use: Private Sub Workbook_Activate() startappmon End Sub I use this technique in several applications where I activate and deactivate toolbars depending on which file is open. hope this is helpful. *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* aju chacko ajuvcha...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Mon, September 26, 2011 5:53:07 AM *Subject:* Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook dear sam, Thanks for your kindness.I have imported all the module as u mentioned,But the following line in class module is throwing an error(syntax error) ' If Not IsEmpty(ActiveCell) Then' But i didn't see anything wrong with this line.By the way i am using excel 2007 and my purpose is to shuttle data between 'Excel' and 'SAP' applications.My purpose is to copy data from the cell once I select Excel window , Now i am running this macro by selecting excel window then pre the short cut key assigned to the macro for copying the data every time.Kindly help me.Thanking you in advance aju v chacko On 9/26/11, Sam Mathai Chacko samde...@gmail.com wrote: This is how you do it. Code below 'In Module Option Explicit Dim objApp As New Class1 Sub Auto_Open() Set objApp.app = Application End Sub Sub Auto_Close() Set objApp = Nothing End Sub Sub startappmon() If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If ActiveCell.Value Is Not Empty Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If End Sub 'In Class (name of class here is Class1) Option Explicit Public WithEvents app As Application Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If Not IsEmpty(ActiveCell) Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If End Sub Regards, Sam On Mon, Sep 26, 2011 at 10:33 AM, aju chacko ajuvcha...@gmail.com wrote: Dear friends, I have created the following code to to trap windowactivate event and execute a code from personal macro workbook when the activate sheet name is sap vs phy.But
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
dear sam/paul, Thanks for your guidance regards aju On 9/27/11, Sam Mathai Chacko samde...@gmail.com wrote: Ah! now you are talking. Aju, unfortunately there is no application level even that triggers when the application is active. The application class events are associated to the workbook events and below. And I am not sure if an API can be used to do this. the other suggestion I have is to use a scheduled event for every second that will run in the background and run when the app is active, but that could be flawed. Regards, Sam On 9/26/11, Paul Schreiner schreiner_p...@att.net wrote: It looks like you are correct! the Workbook_Activate event only triggers when switching between Excel workbooks. It looks like there should be an Application.WindowActivate event, but I haven't been able to get it to work in the small test case I tried. I also found an event that requires a class module, but have not looked at it further. You COULD create a SelectionChange event, that would trigger if a cell is selected, but that would require you to select the Excel application, AND select a cell. (any cell would do) I'll try to look further. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: aju chacko ajuvcha...@gmail.com To: excel-macros@googlegroups.com Sent: Mon, September 26, 2011 1:10:44 PM Subject: Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook Dear sam/paul Isempty(activecell.value) is OK now.But the macro doesn't work as i expected,It triggers only once.Actually what i want is whenever I switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro should execute. As i have understood,the workbook_activate event triggers only when we are switching between excel windows.Kindly help Aju v chacko On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko samde...@gmail.com wrote: It shouldn't be throwing an error, unless there is no workbook at all. There are lots of ways to check whether a cell is empty. Try this. If Len(ActiveCell.Value)=0 Then OR If ActiveCell.Value= Then Paul, the event that Aju is after, is for an add-in to identify whenever a workbook with the mentioned sheet name is activated at an application level. The event which you mentioned is at the Workbook level (associated with the file that it is written in). Aju requires the event at an application level. Let me know if you need further clarification. Regards Sam Mathai Chacko On Mon, Sep 26, 2011 at 4:42 PM, Paul Schreiner schreiner_p...@att.net wrote: Forgive me if this is totally off-base. But I have never used the Activate event in a Class module. In the ThisWorkbook module, I would use: Private Sub Workbook_Activate() startappmon End Sub I use this technique in several applications where I activate and deactivate toolbars depending on which file is open. hope this is helpful. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: aju chacko ajuvcha...@gmail.com To: excel-macros@googlegroups.com Sent: Mon, September 26, 2011 5:53:07 AM Subject: Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook dear sam, Thanks for your kindness.I have imported all the module as u mentioned,But the following line in class module is throwing an error(syntax error) ' If Not IsEmpty(ActiveCell) Then' But i didn't see anything wrong with this line.By the way i am using excel 2007 and my purpose is to shuttle data between 'Excel' and 'SAP' applications.My purpose is to copy data from the cell once I select Excel window , Now i am running this macro by selecting excel window then pre the short cut key assigned to the macro for copying the data every time.Kindly help me.Thanking you in advance aju v chacko On 9/26/11, Sam Mathai Chacko samde...@gmail.com wrote: This is how you do it. Code below 'In Module Option Explicit Dim objApp As New Class1 Sub Auto_Open() Set objApp.app = Application End Sub Sub Auto_Close() Set objApp = Nothing End Sub Sub startappmon() If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy
$$Excel-Macros$$ Trap window activate event from personal macro workbook
Dear friends, I have created the following code to to trap windowactivate event and execute the code from personal macro workbook when the activate sheet name is sap vs phy.But it works only when the excel is opened for first time.Kindly check the code give necessary correction so that once maco is executed whenever excel window is activated w.shhet name is sap vs phy then the following code is executed -- -- 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
$$Excel-Macros$$ Trap window activate event from personal macro workbook
Dear friends, I have created the following code to to trap windowactivate event and execute a code from personal macro workbook when the activate sheet name is sap vs phy.But it works only when the excel is opened for first time.Kindly check the code give necessary correction so that once maco is executed whenever excel window is activated w.shhet name is sap vs phy then the following code is executed If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If ActiveCell.Value Is Not Empty Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If The sequence in which i made the modules are as follows . '***macro in personal macro workbook Sub exceltosap() ' ' Macro1 Macro ' startappmon End Sub ... '***macro in pesonal macro workbook Dim x As New Class11 Sub startappmon() Set x.app = Application End Sub .. '***class module in personal macro workbook Public WithEvents app As Application Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) If ActiveSheet.Name = sap vs phy Then If ActiveCell.Column = 1 Then Selection.End(xlToRight).Select Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Range(Selection, Selection.End(xlToLeft)).Select With Selection.Interior .Color = 5287936 End With ActiveCell.End(xlToLeft).Offset(1, 0).Select If ActiveCell.Value Is Not Empty Then Selection.Copy Application.WindowState = xlMinimized 'doappmon Else Exit Sub End If End If End If End Sub -- -- 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 sap vs phy.xls Description: MS-Excel spreadsheet PERSONAL.XLS Description: MS-Excel spreadsheet
Fwd: $$Excel-Macros$$ Help needed to comine VBA modules
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
Re: $$Excel-Macros$$ Help needed to comine VBA modules
Dear Rajanverma, Thanks for your help aju v chacko On Sat, Jul 30, 2011 at 6:33 PM, Rajan_Verma rajanverma1...@gmail.comwrote: 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
Re: Fwd: $$Excel-Macros$$ kindly help in modifying attached macro
dearest paul, Thanks a lot.U are really a friend in need. aju On Thu, Feb 11, 2010 at 9:32 PM, Paul Schreiner schreiner_p...@att.netwrote: What you've asked to do isn't difficult, but it IS time-consuming. This forum isn't designed to be a free programming service site. Most of the users here have jobs, and volunteer their time to help others at THEIR jobs. What you've asked to do looks simple enough, on the surface. But it can get complicated. For instance, in your macro, you're copying the SAP data to the new sheet and adding the PHY values. but you want to be able to SELECT which column in the SAP data has the quantity. what happens to the rest? I put together a userform that allows the user to select the sheet IN THE CURRENT workbook. it then populates the comboboxes with the sheets so that the user can select the material and quantity columns. it then runs a simplified consolidation script. You can try it using the Show_Form macro. Paul -- *From:* aju chacko ajuvcha...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, February 11, 2010 5:06:14 AM *Subject:* Fwd: $$Excel-Macros$$ kindly help in modifying attached macro Any body in the group ,please help it's urgent OR give some guidelines in accepting inputs from users so that existing code can be used aju v chacko -- Forwarded message -- From: aju chacko ajuvcha...@gmail.com Date: Feb 10, 2010 5:44 PM Subject: $$Excel-Macros$$ kindly help in modifying attached macro To: excel-macros@googlegroups.com *Dear vba gurus*, Attached herewith a macro that made by friend to compare two excel work sheets (SAP PHY) in workbook,both are arranged as table *one common field (MATERIAL) *available in both sheets .What the macro is doing that it create w.sheet named consolidated then tabulate *'quantity' field from sheet phy *against *'unrestricted' field* *in w.sheet SAP* for the same *material* in two sheets.Similarly if a item in *MATERIAL* item is not available in w.sheet *SAP*,but available in w.sheet *PHY* that also will be brought to consolidated worksheet with sap qty '*0'*.The difference of these two fields ( *unrestricted-**quantity) in final column with heading 'difference'.Now this macro is HARD CODED so that it work only when specified worksheets,specified column headers etc. in specified order,What i required is a macro when it is executed ,accepts some inputs from the user as shown belo**w * 1] User will be prompted to *select first second work sheets to be compared* among the w.sheets in the active work book 2] Once he selected the w.book(s),he will be prompted to select *the common field from both the sheets* on which comparison to be made. 3] Finally he will be prompted to *select numerical fileds from both sheets* which is to be compared side by side Once he made these selections remaining process is same as what the attached macro is doing (summing numerical field for duplicate entries eliminate duplicate entries ,final using vlookup function to find matches) *except the user selected values to be assigned against the hard coded variable name used in the macro*. Thanking you in advance, Aju v chacko -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com/ 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW
Re: $$Excel-Macros$$ HELP needed for macro
HI ashish, Thanks for reply.But I have some more requirements.The third worksheet consolidated originally doesn't exists,I have created it for an example.Actually the w.sheet named *SAP *will be created by downloading data from *ERP.*The w.sheet *physical* will be added to this file manually.While executing macro from any of this w.sheet,*It has to create the third w.sheet named consolidated,As per the given structure and consolidate fields **unrestricted Quantity based on material** and the diffidence of these fields should come in filed diff*.Hope you will help me.Thanking you in advance. aju v chacko On Sat, Jan 16, 2010 at 1:20 PM, ashish koul koul.ash...@gmail.com wrote: hi check it r u looking for this On Fri, Jan 15, 2010 at 10:28 PM, aju chacko ajuvcha...@gmail.com wrote: HI VBA GURUS, I know excel pretty well,But my knowledge in VBA is big zero.I have two excel work sheets (SAP PHY),both are arranged as table *one common field (MATERIAL) *available in both sheets ,what I required* is a macro* that brings *quantity* from w.sheet PHY against *unrestricted* in w.sheet SAP for the same *material*.*Similarly if a MATERIAL is not available in w.sheet SAP,but in w.sheet PHY that also should come in consolidated workshee*t.The difference of these two fields ( *unrestricted-**quantity) *should appear in field DIFF.Please see the attached file which shows the example.KINDLY HELP,Now I am doing this manually with vlookup function. AJU V CHACKO -- -- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ HELP needed for macro
HI VBA GURUS, I know excel pretty well,But my knowledge in VBA is big zero.I have two excel work sheets (SAP PHY),both are arranged as table *one common field (MATERIAL) *available in both sheets ,what I required* is a macro* that brings *quantity* from w.sheet PHY against *unrestricted* in w.sheet SAP for the same *material*.Similarly if a MATERIAL is not available in w.sheet *SAP*,but in w.sheet *PHY* that also should come in consolidated worksheet.The difference of these two fields ( * unrestricted-**quantity) *should appear in field DIFF.Please see the attached file which shows the example.KINDLY HELP,Now I am doing this manually with vlookup function. AJU V CHACKO -- -- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe SAP.XLS Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Problem with filters in Excel 2007
HI Mahesh Bisht, Please try to keep the cursor in data area instead of column heading while you are copying the data,Also Microsoft has announced a patch to solve this problem which I will give you shortly. AJU On Tue, Oct 20, 2009 at 9:21 AM, Mahesh mahender.bi...@gmail.com wrote: Hi Friends, I m facing a problem while using Auto filter in Excel 2007 When i use filter and copy the data from 1 sheet to another sheet the hidden cell are also copy from 1 to another sheet. Please help to solve this -- With Love, Mahesh Bisht --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
