Dear Ashish,
I was out of town so could not reply earlier
I found the mistake and now it is working fine.
superrrrrrrrrrrrr
thanks a lot
Rashid
On Sunday, October 14, 2012 1:24:38 PM UTC+4, ashish wrote:
> Hi
>
> Its working fine on my desktop. Can you send the file
>
> Also specify which version of office u r using
> Sent on my BlackBerry® from Vodafone
> ------------------------------
> *From: * prkhan56 <prkh...@gmail.com <javascript:>>
> *Sender: * excel-...@googlegroups.com <javascript:>
> *Date: *Sat, 13 Oct 2012 12:01:09 -0700 (PDT)
> *To: *<excel-...@googlegroups.com <javascript:>>
> *ReplyTo: * excel-...@googlegroups.com <javascript:>
> *Subject: *Re: $$Excel-Macros$$ Re: Macro to create Pivot Table for each
> sheet in the workbook
>
> Dear Ashish,
> I am still waiting some guidance from your end
>
> Regards
> Rashid khan
> On Tuesday, October 9, 2012 11:08:19 PM UTC+4, prkhan56 wrote:
>
>> Hello Ashish,
>> Any guidance to solve the problem I am facing?
>> Thanks
>> Rashid Khan
>>
>> On Sunday, October 7, 2012 8:59:04 PM UTC+4, prkhan56 wrote:
>>
>>> Hello Ashish,
>>>
>>> When I run call_pivot
>>> It gives Run-time Error '9'
>>> Subscript out of range
>>> and highlights the following line:
>>>
>>> Call create_pivot_table(Sheets(Sheets("List").Range("a" & i).Value))
>>>
>>> Am I doing something wrong?
>>>
>>> Thanks for your time once again.
>>> Rashid
>>>
>>> On Sunday, October 7, 2012 9:56:27 AM UTC+4, ashish wrote:
>>>>
>>>> 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 <prkh...@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 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.com.
>>>>>>> To unsubscribe from this group, send email to excel-macros...@**
>>>>>>> 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-...@googlegroups.com.
>>>>> To unsubscribe from this group, send email to
>>>>> excel-macros...@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-...@googlegroups.com<javascript:>
> .
> To unsubscribe from this group, send email to
> excel-macros...@googlegroups.com <javascript:>.
>
>
>
--
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.
