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 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-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.
