Re: $$Excel-Macros$$ Re: Help on Macro
Hi Shrinivas, A sample workbook with the parameters and the expected output will be helpful for giving exact solution. *Ms-Exl-Learner* On 06-10-2012 9:01 AM, Shrinivas Shevde wrote: Dear Excel Learner First of all sorry for delay. Thanks for the reply and this is exactly I want. Can u help me little more In a master sheet there is sample Name I want to put 2 or 3 (may be more)more parameter like ,Date, Created by ,Cost etc.and all this should get transfer to respective sheet. All this should be mandatory. means if any of the fields are blank then data should not transfer. Thanks in advance. Regards Shrinivas Dear Don. I appretiate u r effort of understanding my problem. I got solution Thanks for help Regards Shrinivas On Thu, Oct 4, 2012 at 11:50 AM, Ms-Exl-Learner . ms.exl.lear...@gmail.com mailto:ms.exl.lear...@gmail.com wrote: Hi Shrinivas, Copy and paste the below code in Standard Module Option Explicit Sub PreserveData() Dim bWs As Worksheet, sName As String Dim i As Integer, myTemp1 As Byte, myTemp2 As Byte Application.ScreenUpdating = False On Error GoTo ShtMissing Set bWs = Sheets(Master Sheet) sName = bWs.Range(E1).Value If Trim(sName) = Then sName = Def. Sheets.Count End If For i = 1 To Sheets.Count If Sheets(i).Name = sName Then myTemp1 = 1 End If Next i If myTemp1 = 1 Then myTemp2 = MsgBox( Sheet ' sName ' is already exist, _ would you like to replace it? _ , vbQuestion + vbYesNo, Sheet Exist) End If If myTemp2 = 6 Then Application.DisplayAlerts = False Sheets(sName).Delete Application.DisplayAlerts = True ElseIf myTemp2 = 7 Then Exit Sub End If On Error GoTo 0 With bWs.Range(A1).CurrentRegion .AutoFilter Field:=2, Criteria1:= .EntireRow.SpecialCells(xlCellTypeVisible).Copy End With Sheets.Add After:=bWs With ActiveSheet .Name = sName .Paste .Range(A:E).Columns.AutoFit .Range(A1).Select End With bWs.Select Selection.AutoFilter Range(E1).Select MsgBox Sheet ' sName ' is created successfully, vbInformation, Task Completed Application.ScreenUpdating = True Exit Sub ShtMissing: MsgBox Master Sheet is Missing, Unable to continue..., vbCritical, Sheet Missing End Sub Press Alt+F8 and Select “PreserveData” Macro and click Run. Refer the attachment file for details. Hope that helps! Please let us know, in case of any further assistance. *Ms.Exl.Learner* On Mon, Oct 1, 2012 at 10:18 AM, Shrinivas Shevde shrinivas...@gmail.com mailto:shrinivas...@gmail.com wrote: Dear Don, Thanks for reply. Please find details what I want. 1.I have many raw material.Consider I have 50 raw material 2.I want to make a sample with this raw material.While making sample I will use some of the material out of 50 .% of the material wull vary . 3.To do this what I noted down all the raw material in one sheet (master sheet fro raw material) say Sheet No 1. 4.I will make 1st sample with 5 raw material and name it say 111. 5.Now while making another sample say 222 I want to store details of the 1st sample. 6.For that I want a macro so that when I run macro raw material which I used for 1st sample will store in another sheet (As I will required this in future for reference and need to take out print) and master sheet will get empty. 7.In a month I am making more that 65 samples. 8.So I amy required 65 sheet. I hope this is clear if anything is not clear please ask. Regards Shrinivas On Fri, Sep 28, 2012 at 6:16 PM, Don Guillett dguille...@gmail.com mailto:dguille...@gmail.com wrote: What you want is easy to do but I fail to see the LOGIC of what you want. Explain... On Friday, September 28, 2012 5:48:22 AM UTC-5, shrini wrote: Dear All Can Any one help me to write a macro for following things. In sheet 1 I have more than 200 items.I want to make a sample by choosing items from this list. 1.I will make a sample 111 by choosing Items A,C D .(rest items will be there but the percentage column will be blank.) 2.I will make a sample 222 by choosing Items B,E, D 3.Now there should be button if I click on that .Macro will create a new sheet and Details of Sample 111 will copy in that sheet In short whnever I will click on button it will create a new sheet and copy details of that sample. Sample file attached --
Re: $$Excel-Macros$$ Rajan Verma - Most Helpful Member September 2012 *
Congratulation ranjan.keep it up On Sat, Oct 6, 2012 at 11:00 AM, xlstime xlst...@gmail.com wrote: Congratulations Mr. Rajan Ji On Wed, Oct 3, 2012 at 5:13 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: I can say only... यूँ ही बरकरार रखो भाई अपने Expertise के छाप को. और अपनी तरक्की से छोटा कर दो आसमा के माप को. हम दोस्तों की दुआए,सदा संग बन के रहे साए... मेरे ओर से लाख-लाख बधाई, राजन बाबु आपको. -- With Regards, Noorain Ansari http:// http://www.noorainansari.comnoorainansari.comhttp://www.noorainansari.com http:// http://www.excelvbaclinic.blogspot.comexcelvbaclinic.comhttp://www.excelvbaclinic.blogspot.com On Tue, Oct 2, 2012 at 9:03 PM, Ayush Jain jainayus...@gmail.com wrote: [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (jainayus...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D6eraakWfERhOaGRUPfxCBOcbNGNX68vGH8oQ7cfPhdLHOUpZt1Asu2Jw%252BcA9c5FeCp9LWxKP7kiLgYcys3FQElr53dybKoVjF0DhLdg7qbv5Q4u1gYj4XgAprLC8EPVsze6EdLzESBjcEEeAhXrbow%253D%253D%26key%3DMGpXjOUkedzjKZDKlf5Zt4P%252BjqVy4eeFu7qCsKfwRbk%253Dtc=12555466583_2083494728| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=12555466583_2083494728 Dear members, Rajan Verma has been selected as 'Most Helpful Member' for the month of Sep'12. He has posted 137 posts in last month and helped forum members through his excel expertise. I truly appreciate his knowledge, consistency and commitment to the group. * Rajan, please find enclosed the award certificate in honor of your contribution. Congratulations and thanks for your contribution. * Thanks to Vabz, Don, Paul, Prince, Noorain, Kuldeep, David and all other folks for helping excel enthusiasts voluntarily !! Keep posting. Regards, Ayush Jain Group Manager P.S. If you have any feedback, please share your thoughts by filling the survey formhttps://docs.google.com/spreadsheet/viewform?fromEmail=trueformkey=dHpYdmtTeWZBcEhDWDhmandNWDlpYXc6MQ. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- . -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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,
$$Excel-Macros$$ regarding use of double minus
Dear Noorain Ansari, Would you please tell why have you used the -- sing in the formula. for you reference i have highlighted the portion.. *=SUMPRODUCT(**--**(B4:B14=Karthik)*($C$4:$C$14=Collected))* Thanking you Regards, Sanjib -- - -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Re: Help on Macro
Really great that solve my purpose Very very thanks. On Sat, Oct 6, 2012 at 4:08 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.comwrote: Hi Shrinivas, Below is the revised code. '* Option Explicit Sub PreserveData() Dim bWs As Worksheet, sName As String Dim i As Integer, myTemp As Byte Dim shFound As Boolean Application.ScreenUpdating = False On Error GoTo ShtMissing Set bWs = Sheets(Master Sheet) sName = bWs.Range(E1).Value With bWs For i = 1 To 4 If .Cells(i, E) = Then MsgBox Please enter UCase(.Cells(i, E).Offset(0, -1).Value) _ and Run the code again..., vbCritical, _ UCase(.Cells(i, E).Offset(0, -1).Value) is missing Exit Sub End If Next i End With For i = 1 To Sheets.Count If Sheets(i).Name = sName Then shFound = True Exit For End If Next i If shFound Then myTemp = MsgBox( Sheet ' sName ' is already exist, _ would you like to replace it? _ , vbQuestion + vbYesNo, Sheet Exist) End If If myTemp = 6 Then Application.DisplayAlerts = False Sheets(sName).Delete Application.DisplayAlerts = True ElseIf myTemp = 7 Then Exit Sub End If On Error GoTo 0 With bWs.Range(A1).CurrentRegion .AutoFilter Field:=2, Criteria1:= .SpecialCells(xlCellTypeVisible).Copy End With Sheets.Add After:=bWs With ActiveSheet .Name = sName .Paste End With bWs.Select Selection.AutoFilter bWs.Range(D1).CurrentRegion.Copy Sheets(sName).Select Range(D1).PasteSpecial xlPasteAll Range(A:E).Columns.AutoFit Range(A1).Select bWs.Select Application.CutCopyMode = False MsgBox Sheet ' sName ' is created successfully. _ , vbInformation, Task Completed Application.ScreenUpdating = True Exit Sub ShtMissing: MsgBox Master Sheet is Missing, Unable to continue... _ vbCr vbCr * Check whether the sheet is Renamed / Deleted or Moved _ , vbCritical, Sheet Missing End Sub '* Refer the attachment for details. Hope that helps! Let us know, in case of any further assistance. ** *Ms.Exl.Learner* On Sat, Oct 6, 2012 at 1:24 PM, Shrinivas Shevde shrinivas...@gmail.comwrote: Please find attached file . Basicall I want to confirm that all fields (mark in yellow) are filled up before transfering data.i e data will be transfer only if all fields are filled up On Sat, Oct 6, 2012 at 11:44 AM, Ms-Exl-Learner ms.exl.lear...@gmail.com wrote: Hi Shrinivas, A sample workbook with the parameters and the expected output will be helpful for giving exact solution. *Ms-Exl-Learner* On 06-10-2012 9:01 AM, Shrinivas Shevde wrote: Dear Excel Learner First of all sorry for delay. Thanks for the reply and this is exactly I want. Can u help me little more In a master sheet there is sample Name I want to put 2 or 3 (may be more)more parameter like ,Date, Created by ,Cost etc.and all this should get transfer to respective sheet. All this should be mandatory. means if any of the fields are blank then data should not transfer. Thanks in advance. Regards Shrinivas Dear Don. I appretiate u r effort of understanding my problem. I got solution Thanks for help Regards Shrinivas On Thu, Oct 4, 2012 at 11:50 AM, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Hi Shrinivas, Copy and paste the below code in Standard Module Option Explicit Sub PreserveData() Dim bWs As Worksheet, sName As String Dim i As Integer, myTemp1 As Byte, myTemp2 As Byte Application.ScreenUpdating = False On Error GoTo ShtMissing Set bWs = Sheets(Master Sheet) sName = bWs.Range(E1).Value If Trim(sName) = Then sName = Def. Sheets.Count End If For i = 1 To Sheets.Count If Sheets(i).Name = sName Then myTemp1 = 1 End If Next i If myTemp1 = 1 Then myTemp2 = MsgBox( Sheet ' sName ' is already exist, _ would you like to replace it? _ , vbQuestion + vbYesNo, Sheet Exist) End If If myTemp2 = 6 Then Application.DisplayAlerts = False Sheets(sName).Delete Application.DisplayAlerts = True ElseIf myTemp2 = 7 Then Exit Sub End If On Error GoTo 0 With bWs.Range(A1).CurrentRegion .AutoFilter Field:=2, Criteria1:= .EntireRow.SpecialCells(xlCellTypeVisible).Copy End With Sheets.Add After:=bWs With ActiveSheet .Name = sName .Paste .Range(A:E).Columns.AutoFit .Range(A1).Select End With bWs.Select Selection.AutoFilter Range(E1).Select MsgBox Sheet ' sName ' is created successfully, vbInformation, Task Completed Application.ScreenUpdating = True Exit Sub ShtMissing: MsgBox Master Sheet is Missing, Unable to continue..., vbCritical, Sheet Missing End
$$Excel-Macros$$ Fillup data in cell
Dear experts, I need to fill data in downward selected cell from upward cell as mention in attached file, is there possible. Thanks regards. Ashish Bhalara 9624111822 P*Please do not print this email unless it is absolutely necessary. Spread environmental üawareness.♣♣♣* -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Fillup data in cell
visit http://www.contextures.com/xlDataEntry02.html *Fill Non Blank Cells From Above * Step 1 Select the range. http://3.bp.blogspot.com/-AAuPet5u7zM/TnBj_GdU1ZI/ArU/I9KTov6gsDQ/s1600/fill%2Bblank%2Bfrom%2Babove%2B1.JPG Step 2 Press F5 -Special - choose Blanks http://1.bp.blogspot.com/-UrWcMQv8CV4/TnBk2MwOj9I/Ark/gtl5iVO0qlc/s1600/fill%2Bblank%2Bfrom%2Babove%2B2.JPG Step 3. http://4.bp.blogspot.com/-XBtqunTk5DI/TnBk84b2sJI/Ars/ag9sAhQSMGI/s1600/fill%2Bblank%2Bfrom%2Babove%2B3.JPG On Sat, Oct 6, 2012 at 4:54 PM, Ashish Bhalara ashishbhalar...@gmail.comwrote: Dear experts, I need to fill data in downward selected cell from upward cell as mention in attached file, is there possible. Thanks regards. Ashish Bhalara 9624111822 P*Please do not print this email unless it is absolutely necessary. Spread environmental üawareness.♣♣♣* -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ regarding use of double minus
visit http://www.excelbanter.com/showthread.php?t=182549 On Sat, Oct 6, 2012 at 2:22 PM, Sanjib Chatterjee chatterjee.kolk...@gmail.com wrote: Dear Noorain Ansari, Would you please tell why have you used the -- sing in the formula. for you reference i have highlighted the portion.. *=SUMPRODUCT(**--**(B4:B14=Karthik)*($C$4:$C$14=Collected))* Thanking you Regards, Sanjib -- - -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Forms Controls and ActiveX Controls
http://peltiertech.com/WordPress/forms-controls-and-activex-controls-in-excel/ http://excelexperts.com/Form-Control-vs-ActiveX-Control http://office.microsoft.com/en-us/excel-help/overview-of-forms-form-controls-and-activex-controls-on-a-worksheet-HA010237663.aspx http://www.dailydoseofexcel.com/archives/2004/08/11/controls-activex-vs-forms/ On Sat, Oct 6, 2012 at 1:25 PM, Suman ksuman1...@gmail.com wrote: Dear Experts!! Please guide me that how to use Forms Controls and ActiveX Controls in excel. -- *Suman Kumar* -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. A_html_m691da4f3.gif
$$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook
On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote: I posted this yesterday...it seems it did not get through..so posting once again Hello All, I am using Excel 2010. I have a List of Names in Sheet name “List” I have attached a file with two modules. *Module1* Macro creates separate sheet with the names listed in the sheet name List and copies Sheet Data for each name. *Module2* makes Pivot Table on the Active Sheet. My requirement is to amend the *Module2* as follows: 1) * **Module2* should loop through each name and create Pivot Table for each sheet created and name it viz. Pivot created for Name1 should be PivotName1, Pivot created for Name2 should be PivotName2 and so on. 2) The macro should exclude Data and List sheets. Any help would be appreciated. Thanks Rashid Khan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook
run a loop and pass sheet names to sub routine create_pivot_table Sub call_pivot() Call create_pivot_table(Sheets(Participant1)) End Sub Sub create_pivot_table(wk As Worksheet) Application.DisplayAlerts = False Dim pt As PivotTable Dim PTCache As PivotCache Dim wks As Worksheet For Each wks In ThisWorkbook.Sheets If UCase(PT wk.Name) = UCase(wks.Name) Then wks.Delete Exit For End If Next wk.Select Sheets.Add ActiveSheet.Name = PT wk.Name Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, wk.Range(a2:i wk.Range(a65356).End(xlUp).Row)) Set pt = PTCache.CreatePivotTable(Range(A1), Pivot1, True) With pt .PivotFields(No).Orientation = xlRowField With .PivotFields(Score) .Orientation = xlDataField .Function = xlSum .Position = 1 End With With .PivotFields(Result) .Orientation = xlDataField .Function = xlSum .Position = 2 End With With .PivotFields(Skills %) .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = 0% End With End With Application.DisplayAlerts = True End Sub On Thu, Oct 4, 2012 at 1:20 AM, prkhan56 prkha...@gmail.com wrote: On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote: I posted this yesterday...it seems it did not get through..so posting once again Hello All, I am using Excel 2010. I have a List of Names in Sheet name “List” I have attached a file with two modules. *Module1* Macro creates separate sheet with the names listed in the sheet name List and copies Sheet Data for each name. *Module2* makes Pivot Table on the Active Sheet. My requirement is to amend the *Module2* as follows: 1) * **Module2* should loop through each name and create Pivot Table for each sheet created and name it viz. Pivot created for Name1 should be PivotName1, Pivot created for Name2 should be PivotName2 and so on. 2) The macro should exclude Data and List sheets. Any help would be appreciated. Thanks Rashid Khan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook
Hello Ashish, Thanks for the code. Do I have to do the looping as follows: Sub call_pivot() Call create_pivot_table(Sheets(Participant1)) Call create_pivot_table(Sheets(Participant2)) Call create_pivot_table(Sheets(Participant3)) Call create_pivot_table(Sheets(Participant4)) Call create_pivot_table(Sheets(Participant5)) End Sub Regards Rashid On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote: run a loop and pass sheet names to sub routine create_pivot_table Sub call_pivot() Call create_pivot_table(Sheets(Participant1)) End Sub Sub create_pivot_table(wk As Worksheet) Application.DisplayAlerts = False Dim pt As PivotTable Dim PTCache As PivotCache Dim wks As Worksheet For Each wks In ThisWorkbook.Sheets If UCase(PT wk.Name) = UCase(wks.Name) Then wks.Delete Exit For End If Next wk.Select Sheets.Add ActiveSheet.Name = PT wk.Name Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, wk.Range(a2:i wk.Range(a65356).End(xlUp).Row)) Set pt = PTCache.CreatePivotTable(Range(A1), Pivot1, True) With pt .PivotFields(No).Orientation = xlRowField With .PivotFields(Score) .Orientation = xlDataField .Function = xlSum .Position = 1 End With With .PivotFields(Result) .Orientation = xlDataField .Function = xlSum .Position = 2 End With With .PivotFields(Skills %) .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = 0% End With End With Application.DisplayAlerts = True End Sub On Thu, Oct 4, 2012 at 1:20 AM, prkhan56 prkh...@gmail.com javascript:wrote: On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote: I posted this yesterday...it seems it did not get through..so posting once again Hello All, I am using Excel 2010. I have a List of Names in Sheet name “List” I have attached a file with two modules. *Module1* Macro creates separate sheet with the names listed in the sheet name List and copies Sheet Data for each name. *Module2* makes Pivot Table on the Active Sheet. My requirement is to amend the *Module2* as follows: 1) * **Module2* should loop through each name and create Pivot Table for each sheet created and name it viz. Pivot created for Name1 should be PivotName1, Pivot created for Name2 should be PivotName2 and so on. 2) The macro should exclude Data and List sheets. Any help would be appreciated. Thanks Rashid Khan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received
Re: $$Excel-Macros$$ Convert numbers into HH:MM:SS
GOTTIT the 2.5 is in units of 100 minutes In B1 put = A1*100/24 Format B1 as hh:mm:ss Regards David Grugeon On 7 October 2012 03:42, santosh subudhi santoshkumar.subu...@gmail.com wrote: Hi Group, I want to convert numbers into hrs and mins. Suppose if I enter 2.5 in Cell A1 it should automatically convert it into HH:MM:SS ie 4:10:00 in cell B1 -- Regards Santosh santoshkumar.subu...@gmail.com -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Convert numbers into HH:MM:SS
Sorry should be In B1 put = A1*100/24/60 Regards David Grugeon On 7 October 2012 10:06, David Grugeon da...@grugeon.com.au wrote: GOTTIT the 2.5 is in units of 100 minutes In B1 put = A1*100/24 Format B1 as hh:mm:ss Regards David Grugeon On 7 October 2012 03:42, santosh subudhi santoshkumar.subu...@gmail.com wrote: Hi Group, I want to convert numbers into hrs and mins. Suppose if I enter 2.5 in Cell A1 it should automatically convert it into HH:MM:SS ie 4:10:00 in cell B1 -- Regards Santosh santoshkumar.subu...@gmail.com -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ User form Query box
This group help me a lot for ma assignment time to time, I m really happy to be the member of this group. here again, I've some query require help from expert of this group. I need to create a user input form. I've a list of data in a particular excel spreadsheet, what I want is as soon i entered the data in input box of excel that run a query (may be with vlookup or find command) return me with the result that Data Available or Data Not Available, if the data is available than, it should store in separate sheet or else make input box blank again to enter next record. all the data would be in text format unique. Request your help to get a sample sheet with the above condition, Thanks for help in this matter. Thanks Regards, Prabhat Shrivastava -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ User form Query box
can you share the sample file On Sun, Oct 7, 2012 at 11:03 AM, prabhat.shrivasta...@gmail.com prabhat.shrivasta...@gmail.com wrote: This group help me a lot for ma assignment time to time, I m really happy to be the member of this group. here again, I've some query require help from expert of this group. I need to create a user input form. I've a list of data in a particular excel spreadsheet, what I want is as soon i entered the data in input box of excel that run a query (may be with vlookup or find command) return me with the result that Data Available or Data Not Available, if the data is available than, it should store in separate sheet or else make input box blank again to enter next record. all the data would be in text format unique. Request your help to get a sample sheet with the above condition, Thanks for help in this matter. Thanks Regards, Prabhat Shrivastava -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Macro for Insert and delete worksheets according the list in sheet1
Dear all, Good Morning. I want a macro which can create/ insert/ or delete worksheet according to the list prepared in sheet1. Like if I delete a name from the list the corresponding sheet will be deleted or if I insert a name in the list it will create a sheet with the same name. It will be better for me if the arrangement of the sheets are in the same arrangement the list is. Thanking you in advance. Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Macro for Insert and delete worksheets according the list in sheet1
share the sample file On Sun, Oct 7, 2012 at 11:22 AM, chhajersand...@gmail.com wrote: Dear all, Good Morning. I want a macro which can create/ insert/ or delete worksheet according to the list prepared in sheet1. Like if I delete a name from the list the corresponding sheet will be deleted or if I insert a name in the list it will create a sheet with the same name. It will be better for me if the arrangement of the sheets are in the same arrangement the list is. Thanking you in advance. Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Regards* * * *Ashish Koul* *Visit* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* P Before printing, think about the environment. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each sheet in the workbook
try this Sub call_pivot() Dim i As Long For i = 2 To Sheets(List).Range(a65356).End(xlUp).Row Call create_pivot_table(Sheets(Sheets(List).Range(a i).Value)) Next End Sub Sub create_pivot_table(wk As Worksheet) Application.DisplayAlerts = False Dim pt As PivotTable Dim PTCache As PivotCache Dim wks As Worksheet For Each wks In ThisWorkbook.Sheets If UCase(PT wk.Name) = UCase(wks.Name) Then wks.Delete Exit For End If Next wk.Select Sheets.Add ActiveSheet.Name = PT wk.Name Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, wk.Range(a2:i wk.Range(a65356).End(xlUp).Row)) Set pt = PTCache.CreatePivotTable(Range(A1), Pivot1, True) With pt .PivotFields(No).Orientation = xlRowField With .PivotFields(Score) .Orientation = xlDataField .Function = xlSum .Position = 1 End With With .PivotFields(Result) .Orientation = xlDataField .Function = xlSum .Position = 2 End With With .PivotFields(Skills %) .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = 0% End With End With Application.DisplayAlerts = True End Sub On Sun, Oct 7, 2012 at 12:44 AM, prkhan56 prkha...@gmail.com wrote: Hello Ashish, Thanks for the code. Do I have to do the looping as follows: Sub call_pivot() Call create_pivot_table(Sheets(**Participant1)) Call create_pivot_table(Sheets(**Participant2)) Call create_pivot_table(Sheets(**Participant3)) Call create_pivot_table(Sheets(**Participant4)) Call create_pivot_table(Sheets(**Participant5)) End Sub Regards Rashid On Saturday, October 6, 2012 9:14:07 PM UTC+4, ashish wrote: run a loop and pass sheet names to sub routine create_pivot_table Sub call_pivot() Call create_pivot_table(Sheets(**Participant1)) End Sub Sub create_pivot_table(wk As Worksheet) Application.DisplayAlerts = False Dim pt As PivotTable Dim PTCache As PivotCache Dim wks As Worksheet For Each wks In ThisWorkbook.Sheets If UCase(PT wk.Name) = UCase(wks.Name) Then wks.Delete Exit For End If Next wk.Select Sheets.Add ActiveSheet.Name = PT wk.Name Set PTCache = ActiveWorkbook.PivotCaches.**Create(xlDatabase, wk.Range(a2:i wk.Range(a65356).End(xlUp).**Row)) Set pt = PTCache.CreatePivotTable(**Range(A1), Pivot1, True) With pt .PivotFields(No).Orientation = xlRowField With .PivotFields(Score) .Orientation = xlDataField .Function = xlSum .Position = 1 End With With .PivotFields(Result) .Orientation = xlDataField .Function = xlSum .Position = 2 End With With .PivotFields(Skills %) .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = 0% End With End With Application.DisplayAlerts = True End Sub On Thu, Oct 4, 2012 at 1:20 AM, prkhan56 prkh...@gmail.com wrote: On Monday, October 1, 2012 9:11:00 PM UTC+4, prkhan56 wrote: I posted this yesterday...it seems it did not get through..so posting once again Hello All, I am using Excel 2010. I have a List of Names in Sheet name “List” I have attached a file with two modules. *Module1* Macro creates separate sheet with the names listed in the sheet name List and copies Sheet Data for each name. *Module2* makes Pivot Table on the Active Sheet. My requirement is to amend the *Module2* as follows: 1) * **Module2* should loop through each name and create Pivot Table for each sheet created and name it viz. Pivot created for Name1 should be PivotName1, Pivot created for Name2 should be PivotName2 and so on. 2) The macro should exclude Data and List sheets. Any help would be appreciated. Thanks Rashid Khan -- Join official facebook page of this forum @ https://www.facebook.com/** discussexcel https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or
